| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a named range on another sheet | Excel Worksheet Functions | |||
| Named range in a sheet referred to from another sheet | Excel Programming | |||
| Recalculate Named Range After Sheet Changes | Excel Programming | |||
| Loop thru named range of second sheet | Excel Programming | |||
| Referencing Named Range in Other Sheet | Excel Programming | |||
![Zone[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
