Which copy/paste option

[LaBamba]

Hi all,

I want to copy and paste a range based on values found in column A. The values will also be a range...so items that are between 15-2000 for example need to be copied and pasted to "sheetname1" values between 2001-5000 will be pasted to "sheetname2"

Is it best to name my range first or use another method to copy range? The range will be all information on the sheet based on value in first column.

Let me know your thoughts.

Thanks,

[p45cal]

If this is a one-off operation (or very rare) then I would use Autofilter, set your filter up for rows destined for sheetname1 by using the Number filters part of the filter ni column A and selecting Between…
once filtered, you can do a plain copy paste of the visible remaining rows. Then change the filter and repeat.

[LaBamba]

Ended up using the following code...worked great

Dim lr As Long
Dim lr2 As Long
Dim lr3 As Long
Dim r As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim n As Long




Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("GS")
Set ws3 = Sheets("PS")


ws2.UsedRange.Offset(1).Clear
n = 1
lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
If Range("A" & r).Value >= 800000 Then
Rows(r).Copy Destination:=ws2.Range("a" & n + 1)
n = ws2.Cells(Rows.Count, "a").End(xlUp).Row
End If
Next r
ws1.Select

ws3.UsedRange.Offset(1).Clear
n = 1
lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr3 = ws3.Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
If Range("A" & r).Value < 800000 Then
Rows(r).Copy Destination:=ws3.Range("a" & n + 1)
n = ws3.Cells(Rows.Count, "a").End(xlUp).Row
End If
Next r
ws1.Select


ActiveWorkbook.Close savechanges:=True
wbCurrent.Close savechanges:=True

End sub