0

it paste the value but not to the last rows in the sheet (List) the END(xlDown).offset(1) stop the macros and make it crash, I get error 1004 object undefined.

Can someone help me better up this code>

Private Sub CommandButton2_Click()

Dim ws As Worksheet
Set ws = ActiveSheet

Worksheets("FORM").Range("B7").Copy
Worksheets("List").Range("B7").End(xlDown).Offset(1).PasteSpecial xlPasteValues
Worksheets("FORM").Range("D7").Copy
Worksheets("List").Range("C7").PasteSpecial xlPasteValues
Worksheets("FORM").Range("G7").Copy
Worksheets("List").Range("D7").PasteSpecial xlPasteValues
'second line in the same row'
Worksheets("FORM").Range("A8").Copy
Worksheets("List").Range("F7").PasteSpecial xlPasteValues
Worksheets("FORM").Range("E8").Copy
Worksheets("List").Range("G7").PasteSpecial xlPasteValues
Worksheets("FORM").Range("H8").Copy
Worksheets("List").Range("H7").PasteSpecial xlPasteValues
Worksheets("FORM").Range("K8").Copy
Worksheets("List").Range("I7").PasteSpecial xlPasteValues

End Sub
Farida
  • 1
  • 1
  • 1
    See [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. – BigBen Feb 10 '20 at 21:17

1 Answers1

0

This is because you're using Worksheets("FORM") when you should be using Sheets("FORM") Also, when you're using .Offset() you need to specify the row AND column numbers you want to ofset by - so .Offset(1,0) in your case I imagine. If you make those changes, your code should work.

newuser2967
  • 316
  • 1
  • 4
  • 15
  • I did, but I get this " Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score." – Farida Feb 18 '20 at 01:03
  • Yes, but there's a tick button to accept an answer if it has worked for you - different to the up and down arrows. – newuser2967 Feb 18 '20 at 07:45