Consulting

Results 1 to 3 of 3

Thread: Which copy/paste option

  1. #1
    VBAX Regular
    Joined
    Jun 2014
    Posts
    19
    Location

    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,

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,021
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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.

  3. #3
    VBAX Regular
    Joined
    Jun 2014
    Posts
    19
    Location
    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
  •