Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Named Ranges with VBA
I have programmatically created a named range that is tied to a worksheet
using the following Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange What would change if the range name was not tied to the worksheet, but global for the wookbook? Thanks, Barb Reinhardt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Named Ranges with VBA
What would change if the range name was not tied to the worksheet, but
global for the wookbook? rangename would not have the sheetname as part of the string. HTH, Bernie MS Excel MVP "Barb Reinhardt" wrote in message ... I have programmatically created a named range that is tied to a worksheet using the following Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange What would change if the range name was not tied to the worksheet, but global for the wookbook? Thanks, Barb Reinhardt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Named Ranges with VBA
I don't think I made myself clear. I believe that there are range names
that are global within the workbook and range names that only work on the current worksheet. This example: Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange Created a named range that was only available on sht. How would I create a named range using VBA that's available on all sheets. If you could provide the syntax, I'd appreciate it. Thanks, Barb "Bernie Deitrick" wrote: What would change if the range name was not tied to the worksheet, but global for the wookbook? rangename would not have the sheetname as part of the string. HTH, Bernie MS Excel MVP "Barb Reinhardt" wrote in message ... I have programmatically created a named range that is tied to a worksheet using the following Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange What would change if the range name was not tied to the worksheet, but global for the wookbook? Thanks, Barb Reinhardt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Named Ranges with VBA
Worksheets(1).Names.Add Name:="Bob", RefersTo:="=A1:A10"
ActiveWorkbook.Names.Add Name:="Bob", RefersTo:="=M1:M10" -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Barb Reinhardt" wrote in message ... I don't think I made myself clear. I believe that there are range names that are global within the workbook and range names that only work on the current worksheet. This example: Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange Created a named range that was only available on sht. How would I create a named range using VBA that's available on all sheets. If you could provide the syntax, I'd appreciate it. Thanks, Barb "Bernie Deitrick" wrote: What would change if the range name was not tied to the worksheet, but global for the wookbook? rangename would not have the sheetname as part of the string. HTH, Bernie MS Excel MVP "Barb Reinhardt" wrote in message ... I have programmatically created a named range that is tied to a worksheet using the following Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange What would change if the range name was not tied to the worksheet, but global for the wookbook? Thanks, Barb Reinhardt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Named Ranges with VBA
Sorry, I always create local names like this, to the names collection of the workbook: ActiveWorkbook.Names.Add Name:="Sheet1!Test2", RefersToR1C1:="=Sheet1!R6C2" So, to create a global name using your syntax, instead of adding the name to the Worksheets(sht).Names collection, use ActiveWorkbook.Names.Add Name:=rangename, RefersTo:="=" & seriesrange HTH, Bernie MS Excel MVP "Barb Reinhardt" wrote in message ... I don't think I made myself clear. I believe that there are range names that are global within the workbook and range names that only work on the current worksheet. This example: Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange Created a named range that was only available on sht. How would I create a named range using VBA that's available on all sheets. If you could provide the syntax, I'd appreciate it. Thanks, Barb "Bernie Deitrick" wrote: What would change if the range name was not tied to the worksheet, but global for the wookbook? rangename would not have the sheetname as part of the string. HTH, Bernie MS Excel MVP "Barb Reinhardt" wrote in message ... I have programmatically created a named range that is tied to a worksheet using the following Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange What would change if the range name was not tied to the worksheet, but global for the wookbook? Thanks, Barb Reinhardt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Named Ranges with VBA
You have your answer, but those worksheet level names are available from other
sheets, too--just use the whole name. If I give A1 on Sheet2 a worksheet level name of myCell, then I can get that value on other sheets by using: =sheet2!myCell And if you don't have Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager, you should get it. You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp Barb Reinhardt wrote: I don't think I made myself clear. I believe that there are range names that are global within the workbook and range names that only work on the current worksheet. This example: Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange Created a named range that was only available on sht. How would I create a named range using VBA that's available on all sheets. If you could provide the syntax, I'd appreciate it. Thanks, Barb "Bernie Deitrick" wrote: What would change if the range name was not tied to the worksheet, but global for the wookbook? rangename would not have the sheetname as part of the string. HTH, Bernie MS Excel MVP "Barb Reinhardt" wrote in message ... I have programmatically created a named range that is tied to a worksheet using the following Worksheets(sht).Names.Add Name:=rangename, RefersTo:="=" & seriesrange What would change if the range name was not tied to the worksheet, but global for the wookbook? Thanks, Barb Reinhardt -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem creating named ranges in a Macro! | Excel Discussion (Misc queries) | |||
Creating dynamic ranges which are named. | Excel Worksheet Functions | |||
Creating Named Ranges with VBA | Excel Programming | |||
Creating dymnamic named ranges | Excel Programming | |||
Creating Named Ranges in VBA | Excel Programming |