revise macro to use cell reference

Hello Adnan,

Add a string variable to hold the new range address. I've added into the macro for you.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/01/2004
'

Dim NewRng As String

Workbooks.Open Filename:= "Book2.xls"
NewRange = Range("A1").Value
Range(NewRange).Copy Destination:=Workbooks("Book1").Range(NewRange)
Windows("Book2.xls").Close
MsgBox ("Completed Copying")
End Sub

Sincerely,
Leith Ross

Originally Posted by Leith Ross

Hello Adnan,

Add a string variable to hold the new range address. I've added into the macro for you.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/01/2004
'

Dim NewRng As String

Workbooks.Open Filename:= "Book2.xls"
NewRange = Range("A1").Value
Range(NewRange).Copy Destination:=Workbooks("Book1").Range(NewRange)
Windows("Book2.xls").Close
MsgBox ("Completed Copying")
End Sub

Sincerely,
Leith Ross

Hello Leith,

Thank you very much for the help. I copied this code and run, but it gives the error message;
"Object doesn't support this property or method" at line "Range(NewRange)...Range(NewRange). Did I do anything wrong?

Hello Adnan,

What is in cell "A1"? Do you have a range address like "E:F"? If so, you will need to add rows to that address like "E10:F50".

Let me know,
Leith Ross