CODEWITHSUNDEEP
vbaexcel
Zajonc
Zajonc

Reputation: 429

Specifying a range within a workbook without specifying the sheet

If I want to refer to a range within the active workbook in Excel VBA, I can say "With Range("Myrange")". I don't need a sheet name.

Sometimes I want to refer to a range in another workbook. "With MyWorkbook.Range("Myrange")" doesn't work because I have to specify the sheet that the range is in.

Is there any way of referring to the range in a workbook without having to say which sheet the range is in?

Upvotes: 0

Views: 305

Answers (2)

Sobigen
Sobigen

Reputation: 2179

If you create a named range and scope it to the Workbook you can use the following to get a named range in any workbook. You obviously get an error if the name can't be found.

wb.Names("Myrange").RefersToRange

Upvotes: 2

FreeMan
FreeMan

Reputation: 5687

Yes, create a Named Range (ex: MyRangeName) within your workbook. Then in your code use Range("MyRangeName") and it will refer to those cells on that sheet no matter what workbook you're looking at.

Upvotes: 0

Related Questions