Copy and Paste Macro

[dgrowe]

I'd like to copy and paste a range of data using a macro button from one worksheet to another. The data will be variable so I'll need to identify how many rows exist to establish what to copy. I know this is probably fairly easy but I'm a novice with VB code. Thanks.

 

[dgrowe]

Got a run-time error '1004' on the last step:
Sheets("Sheet4").Range("B2").PasteSpecial Paste:=x1PasteValues

Private Sub CommandButton1_Click()
Dim lr As Long
Dim ws As Worksheet

Set ws = Sheets("BUD Total")
lr = ws.Cells(Rows.Count, "I").End(xlUp).Row
ws.Range("I6:I" & lr).Copy
Sheets("Sheet4").Range("A2").PasteSpecial Paste:=xlPasteValues

ws.Range("L6:L" & lr).Copy
Sheets("Sheet4").Range("B2").PasteSpecial Paste:=x1PasteValues

End Sub

 

[Smitty]

Yeah. Sorry, meant A2.

With A2 you'll have the same issue if either of the ranges are greater than one cell; what's copied second will overwrite what's copied first.

 

[dgrowe]

Meant B2. Guess I should figure out how to post screen shots so I don't create confusion on these silly typing mistakes. My previously post shows my current macro and the error which I am receiving.

 

[Smitty]

You've got a typo:

x1PasteValues

It should be XL, not, X1.

 

[dgrowe]

Great! Thanks for your help.