View Full Version : Simple Copy and paste
maninjapan
06-17-2010, 09:36 AM
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
06-17-2010, 09:40 AM
You should, but unless you have Option Explicit then it will/should work.
What is LastRow when you run it?
maninjapan
06-17-2010, 09:48 AM
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
06-17-2010, 09:50 AM
I mean put a break in the code and see what the value of LastRow is?
maninjapan
06-17-2010, 09:57 AM
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
06-17-2010, 10:47 AM
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
06-17-2010, 10:52 AM
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
06-17-2010, 11:54 AM
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
06-17-2010, 12:04 PM
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
06-17-2010, 12:06 PM
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
06-17-2010, 12:19 PM
much appreciated xld, however now getting an error Object doesnt support this property or method...
Btw added end with
maninjapan
06-18-2010, 03:29 AM
Seems to be working fine now, thanks for the help!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.