Greater than, less than, cut and paste rows to sheet3

[Withnails]

Hello
I am looking for a way to grab data in sheet1 column Y that is >0 or <0 (ie
any number that isnt 0). I then want to take those rows where data is
located, cut and paste them into Sheet3, starting at B23.
Does anyone know a good way of doing this? Thank you

 

[Gord Dibben]

Autofilter on not equal to 0

F5>Special to select visible rows.

Copy and paste to Sheet3 at B23

Go back to source sheet which still has visible cells selected and
Delete>Entire Row.


Gord Dibben MS Excel MVP

 

[john]

I have done this very quickly before i leave office & may need some correction.
Hopefully will do what you want or give you some further ideas.

Sub CopyFilterData()
Dim rng As Range
Dim rng2 As Range
Dim ws1 As Worksheet

Set ws1 = Worksheets("Sheet1")

On Error GoTo exitprog
ws1.Range("Y1").AutoFilter _
field:=1, _
Criteria1:="<>0", _
VisibleDropDown:=False

Set rng = ws1.AutoFilter.Range
Set rng = rng.Resize(rng.Rows.Count - 1)


Set rng2 = ws1.Range("Y1:Y" & rng.Count)


If Not rng2 Is Nothing Then
rng2.Copy Destination:=Sheets("Sheet3").Range("B23")
End If

rng.AutoFilter

exitprog:

On Error GoTo 0

End Sub

 

[Withnails]

thank you john, its nearly there, but doesnt copy the entire row? any clues?
thank you

 

[john]

sorry, did not pick that requirment up.

see if this helps:

Sub CopyFilterData()
Dim rng As Range
Dim rng2 As Range
Dim ws1 As Worksheet

Set ws1 = Worksheets("Sheet1")

On Error GoTo exitprog
ws1.Range("Y1").AutoFilter _
field:=1, _
Criteria1:="<>0", _
VisibleDropDown:=False

Set rng = ws1.AutoFilter.Range
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)


Set rng2 = rng.SpecialCells(xlCellTypeVisible)


If Not rng2 Is Nothing Then
rng2.Copy Destination:=Sheets("Sheet3").Range("B23")
End If

rng.AutoFilter

exitprog:

On Error GoTo 0

End Sub