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

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

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

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

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

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