Which Sheet Is Named Range On?
 

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

Which Sheet Is Named Range On?
 

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)

"Zone" wrote in message
oups.com...
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

Which Sheet Is Named Range On?
 

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
--


Gary


"Zone" wrote in message
oups.com...
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

Which Sheet Is Named Range On?
 

Sub demo()
MsgBox (Range("happy").Parent.Name)
End Sub

--
Gary''s Student - gsnu200732


"Zone" wrote:

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

Which Sheet Is Named Range On?
 

i couldn't remember parentname

--


Gary


"Gary''s Student" wrote in message
...
Sub demo()
MsgBox (Range("happy").Parent.Name)
End Sub

--
Gary''s Student - gsnu200732


"Zone" wrote:

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

Which Sheet Is Named Range On?
 

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

On Jun 22, 3:40?pm, "Chip Pearson" wrote:
Try something like

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

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consultingwww.cpearson.com
(email on the web site)

"Zone" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Which Sheet Is Named Range On?
 

One more option:

ActiveWorkbook.Names("TheName").RefersToRange.Pare nt.Name

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Zone" wrote in message
oups.com...
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

Which Sheet Is Named Range On?
 

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").Refer sToRange.Parent.Name

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

On Jun 23, 12:10?pm, "Jon Peltier"
wrote:
One more option:

ActiveWorkbook.Names("TheName").RefersToRange.Pare nt.Name

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"Zone" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Which Sheet Is Named Range On?
 

Should work fine.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Zone" wrote in message
oups.com...
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").Refer sToRange.Parent.Name

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

On Jun 23, 12:10?pm, "Jon Peltier"
wrote:
One more option:

ActiveWorkbook.Names("TheName").RefersToRange.Pare nt.Name

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"Zone" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -