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
--
jb


"Withnails" wrote:

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

"john" wrote:

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
--
jb


"Withnails" wrote:

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