Which Sheet Is Named Range On?

  • Thread starter Thread starter Zone
  • Start date Start date
Z

Zone

I have a workbook with several worksheets. Each worksheet has many
named ranges (single-cell ranges, all in row 1). I want to specify a
range name and have my subroutine activate the sheet containing the
range name and select the column containing the range name.
Thanks, James
 
Try something like

With Range("TheName")
.Worksheet.Select
.Worksheet.Cells(1, .Column).EntireColumn.Select
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
someone might have a better solution, but this works
where test is the name of the range:

shname = Split(Mid(Range("test").Name, 2, 100), "!")
Worksheets(shname(0)).Select

and if you wanted to cycle through all range names

For Each nm In ThisWorkbook.Names
shname = Split(Mid(Range(nm).Name, 2, 100), "!")
Worksheets(shname(0)).Select
Next nm
End Sub
 
Wow, super advice! Thanks, Chip, Gary and Gary''s. Chip, your
example worked perfectly for me. But I will study all the replies!
Tally-ho! James
 
Thank you, Jon. This fills in the last piece of the puzzle. I found
that the workbook in question may not be active when I'm trying to
find the range it. Although I can activate the workbook and then look
for the range's sheet, that's inconvenient and slow in my situation.
So, following your advice, I could apparently use code like

Workbooks("myworkbook.xls").Names("TheName").RefersToRange.Parent.Name

I haven't actually tested this yet, but seems like it should work.
Regards, James
 
Back
Top