Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    Hi good morning, i have the ode below where I am trying to copy all the data in a sheet called 'New' and paste into the last row in a sheet called 'Combined but its not working for me can you help please.

    Code:
    Private Sub CommandButton3_Click()
    Application.ScreenUpdating = False
      Dim copySheet As Worksheet
      Dim pasteSheet As Worksheet
    
      Set copySheet = Worksheets("New")
      Set pasteSheet = Worksheets("Combined")
      copySheet.Range("A:BE").Copy
      pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      Application.CutCopyMode = False
      Application.ScreenUpdating = True
      End Sub

  2. #2
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    I take it you're getting a Copy area and paste area aren't the same size. Range("A:BE") are whole columns. Only copy the required range - rows 1:1048576 won't paste into rows 1000:1048576 manually or via VBA.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  3. #3
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    Hi sorry I don't know what you mean, I am still new to this, I just want to copy and paste to last used row. and to be all the same size etc. hope you can help

  4. #4
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    Something like:

    Code:
    Private Sub Test()
    
      Dim copySheet As Worksheet
      Dim pasteSheet As Worksheet
      Set copySheet = Worksheets("New")
      Set pasteSheet = Worksheets("Combined")
      
    'Only copy the required range.  
    With copySheet
        .Range(.Cells(1, 1), .Cells(Rows.Count, "BE").End(xlUp)).Copy
      End With
      
      pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
    End Sub
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  5. #5
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    Hi thank you for your help this works but only copies the top line, how do I copy all the data from A2:BE in 'New' to last used row into other sheet? thanks again
    Last edited by Patriot2879; May 1st, 2019 at 07:49 AM. Reason: spelling mistake

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Posts
    837
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    Hello Patriot,

    Try the code amended as follows:-

    Code:
    Private Sub CommandButton3_Click()
    
       Dim copySheet As Worksheet
       Dim pasteSheet As Worksheet
       Set copySheet = Worksheets("New")
       Set pasteSheet = Worksheets("Combined")
      
    Application.ScreenUpdating = False
    
    With ws
        .Range(.Cells(2, 1), .Cells(Rows.Count, "BE").End(xlUp)).Copy
        ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End With
     
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    End Sub
    .......or try:-

    Code:
    Private Sub CommandButton3_Click()
     
    Application.ScreenUpdating = False
    
              Dim ws As Worksheet: Set ws = Sheets("New")
              Dim ws1 As Worksheet: Set ws1 = Sheets("Combined")
              Dim lr As Long: lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            
              ws.Range("A2:BE" & lr).Copy
              ws1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
              
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
      
    End Sub

    I hope that this helps.

    Cheerio,
    vcoolio.
    Last edited by vcoolio; May 1st, 2019 at 08:10 AM.

  7. #7
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    Try

    Code:
    With copySheet
        .Range(.Cells(2, "A"), .Cells(.Cells(Rows.Count, "A").End(xlUp).Row, "BE")).Copy
      End With
    Change '.Cells(Rows.Count, "A")' to a column that has data on the last row to copy.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  8. #8
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    thanks that's amazing please can you advise where I went wrong? just for my training knowledge

  9. #9
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    You tried to copy the whole column which is 1048576 rows and paste further down the sheet.

    For example; if you copy A1:A1048576 you're copying 1048576 rows. If you try and paste that into row 11 you'll only have 1048566 rows available to paste into - it won't fit.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  10. #10
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    916
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy data from Sheet1 to sheet2 and Paste Data Below the Last Used Row

    ok thankyou is that because I didn't name the .Range correctly?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •