Find and activate named range in Excel without knowing Worksheet name

1
votes

I'm writing a macro that will update a bunch of workbooks.

Assume the workbook is already open.

I need to activate (or select, either one) a cell that has a name (named range) but the kicker is that I don't know what worksheet it is in.

In other words: I am guaranteed that:

  • The named range exists.
  • The named range is globally unique to the open workbook.

I am not guaranteed that:

  • The named range will be in the same worksheet index.
  • The worksheets will be named the same between workbooks.

Any advice will be appreciated, even one that shows me how to back into a worksheet from a global named range since that will be enough for me to then activate the worksheet and go from there. But ideally the solution will show me how to activate/select/update (the final result would be that I update the cell, or insert rows below if that helps) a named range cell without knowing the worksheet name.

I am doing this in Excel 2007

Thanks!

1
vbaexcel

1 Answers

4
votes

Suppose you have named range "test".

Way 1:

Dim rng As Range
Set rng = Range("test")
'or if you have more than one workbook opened
'Set rng = ThisWorkbook.Names("test").RefersToRange
'select sheet
rng.Parent.Select
'select named range
rng.Select

Way 2: 

'for currently active workbook
Application.Goto "test"