-
Which copy/paste option
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,
-
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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules