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

Autofilter on not equal to 0

F5Special to select visible rows.

Copy and paste to Sheet3 at B23

Go back to source sheet which still has visible cells selected and
DeleteEntire Row.


Gord Dibben MS Excel MVP




On Thu, 1 Oct 2009 08:00:02 -0700, 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

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

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

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