Copy and Paste a set range in the next empty row

Question

This should be simple but I am having a tough time   I want to copy the cells A3 through E3  and paste them into the next empty row on a different worksheet  I have used this code before in longer strings of code   but i had to tweak it and it is not working this time   I get a  application-defined or object-defined error  when i run the code seen below   All help is appreciated   Private Sub CommandButton1 Click   Dim lastrow As Long  lastrow   Range  A65536   End xlUp  row    Range  A3 E3   Copy Destination  Sheets  Summary Info   Range  A A   amp  lastrow  End Sub

User · Answer

You could also try this  Private Sub CommandButton1 Click    Sheets  Sheet1   Range  A3 E3   Copy  Dim lastrow As Long lastrow   Range  A65536   End xlUp  Row  Sheets  Summary Info   Activate Cells lastrow   1  1  PasteSpecial Paste  xlPasteValues  Operation  xlNone  SkipBlanks  False  Transpose  False  End Sub

User · Answer

The reason the code isn t working is because lastrow is measured from whatever sheet is currently active  and  A A500   or other number  is not a valid range reference   Private Sub CommandButton1 Click       Dim lastrow As Long      lastrow   Sheets  Summary Info   Range  A65536   End xlUp  Row      or   1     Range  A3 E3   Copy Destination  Sheets  Summary Info   Range  A   amp  lastrow  End Sub

User · Answer

Below is the code that works well but my values overlap in sheet  Final  everytime the condition of  lt  11 meets in sheet  Calculator   I would like you to kindly support me to modify the code so that the cursor should move to next blank cell and values keeps on adding up like a list   Dim i As Integer Dim ws1 As Worksheet  Set ws1   ThisWorkbook Sheets  Calculator   Dim ws2 As Worksheet  Set ws2   ThisWorkbook Sheets  Final    For i   2 To ws1 Range  A65536   End xlUp  Row      If ws1 Cells i  4   lt   11 Then          ws2 Cells i  1  Value   Left Worksheets  Calculator   Cells i  1  Value  Len Worksheets  Calculator   Cells i  1  Value  - 0          ws2 Cells i  2    Application VLookup Cells i  1   Worksheets  Calculator   Columns  A D    4  False          ws2 Cells i  3    Application VLookup Cells i  1   Worksheets  Calculator   Columns  A E    5  False          ws2 Cells i  4    Application VLookup Cells i  1   Worksheets  Calculator   Columns  A B    2  False          ws2 Cells i  5    Application VLookup Cells i  1   Worksheets  Calculator   Columns  A C    3  False       End If Next i

User · Answer

Be careful with the  Range       without first qualifying a Worksheet because it will use the currently Active worksheet to make the copy from  It s best to fully qualify both sheets  Please give this a shot  please change  Sheet1  with the copy worksheet    EDIT  edited for pasting values only based on comments below   Private Sub CommandButton1 Click     Application ScreenUpdating   False   Dim copySheet As Worksheet   Dim pasteSheet As Worksheet    Set copySheet   Worksheets  Sheet1     Set pasteSheet   Worksheets  Sheet2      copySheet Range  A3 E3   Copy   pasteSheet Cells Rows Count  1  End xlUp  Offset 1  0  PasteSpecial xlPasteValues   Application CutCopyMode   False   Application ScreenUpdating   True End Sub

[vba] Copy and Paste a set range in the next empty row

Examples related to vba

Examples related to excel