Link to home
Start Free TrialLog in
Avatar of Kevin
KevinFlag for United States of America

asked on

Excel 2010 VBA code to select a range using existing Cell names

I want to copy a range from one workbook to another.  The range that I want to copy will always have Cell(1,1) as the top left cell of the range.  The location of the bottom right cell of the range will always be different, BUT it will always have the name "Last_Print_Cell" assigned to it.

How do I select and copy that range using VBA code?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kevin

ASKER

Thanks - I figured it would be easy but could quite get the syntax right.
Sub CopyRange()
Dim newRange As Range
Set newRange = Worksheets(1).Range("A1:Last_Print_Cell")
newRange.Copy Destination:=Worksheets(2).Range("A1")
End Sub

Open in new window

Replace your sheet names and destination with what is appropriate for your workbook.

Regards
Avatar of [ fanpages ]
[ fanpages ]

You're welcome, girbeaud.
I don't expect any points but better would be

Range([A1],[Last_Print_Cell]).Copy
Your suggestion doesn't both select & copy the range though, Martin, as the question required :)