Simple Copy and paste

[maninjapan]

I am trying to create a very simple cut and paste macro, but get an error on the paste line. DO I need to declare last row? (not usre of the syntax if i do.


Sub Macro1()


Range("A1").Copy
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Data").Range("A" & LastRow).Paste
End Sub


thanks in advance

[Bob Phillips]

You should, but unless you have Option Explicit then it will/should work.

What is LastRow when you run it?

[maninjapan]

thanks XLD, I get a run time error '438'; Object doesnt support this property or method.
Im not sure what you mean by what is LastRow when you run it?
Its just the next empty row on the Sheet labelled 'Data', in this case A13

[Bob Phillips]

I mean put a break in the code and see what the value of LastRow is?

[maninjapan]

thanks xld, still very new to this. Just added Debug Print, and it shows the number of the row I expect it to (13 in this case) in the immediate window.

I tried the following but get an error saying object required


Sub Macro1()
'
' Macro1 Macro

Range("A1").Copy
LastRow = Sheets("data").Range("A" & Rows.Count).End(xlUp).Row + 1
Debug.Print LastRow
LastRow.Paste
End Sub

[Bob Phillips]

You have now changed the code. You originalkly had



Sheets("Data").Range("A" & LastRow).Paste


whereas you now have



LastRow.Paste


The first looks ine, the latter is definitely wrong.

[maninjapan]

So if the following is correct, how do I resolve the error Im getting?
This returned the expected value when I replace the paste line with debug.print LastRow



Sub Macro1()


Range("A1").Copy
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Data").Range("A" & LastRow).Paste
End Sub

[mdmackillop]

I would change the order. Also, you should qualify all range references.

Sub Macro1()
With Sheets("Data")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A1").Copy .Range("A" & LastRow)
End With
End Sub

[maninjapan]

md. thanks, it's pasting without the error now, however it is pasting the value from A1 of sheet 'Data', where it should paste the value from a different sheet (in this case Sheet1) to the next empty rtow in sheet 'Data'

[Bob Phillips]

Sub Macro1()
With Worksheets("Data")

LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("Sheet1").Range("A1").Copy .Range("A" & LastRow)
End With
End Sub

[maninjapan]

much appreciated xld, however now getting an error Object doesnt support this property or method...

Btw added end with

[maninjapan]

Seems to be working fine now, thanks for the help!!