Same Name Range for different spreadhseets in a workbook

[Guest]

In previous Excel versions I was able to define a constant in a Name and
assign it to a specific spreadsheet. This is for VBA coding which defines
the action based on the type of spreadsheet that is active. I cannot assign
a spreadsheet specific Name constant within Excel 2003 unless I am missing
something. Attempted to use the Name Add-in Manager from this site but
receive an "On-Click" error so the add-in will not load.

How do I assign the same Name Range to specific spreasheets. For example, I
need a Name Range for every spreadsheet with in the workbook entitled
"SheetType" each spreadsheet will have a different constant assigned to that
Name.

 

[Bob Phillips]

Mark,

Don't have 2003 so I cannot be sure, but I would be surprised if this were
not possible. Did you create it like so

worksheets("Sheet2").Range("A1").Name = "Sheet2!Bob"

and Sheet1, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jan Karel Pieterse]

Hi Mark,

In previous Excel versions I was able to define a constant in a Name and
assign it to a specific spreadsheet. This is for VBA coding which defines
the action based on the type of spreadsheet that is active. I cannot assign
a spreadsheet specific Name constant within Excel 2003 unless I am missing
something. Attempted to use the Name Add-in Manager from this site but
receive an "On-Click" error so the add-in will not load.

Could you send the error message to the address listed on my website please?

How do I assign the same Name Range to specific spreasheets. For example, I
need a Name Range for every spreadsheet with in the workbook entitled
"SheetType" each spreadsheet will have a different constant assigned to that
Name.

Precede the name with the name of the worksheet. If e.g. the worksheet is named
Sheet 1 and the name is Name01, use this name:

'Sheet 1'!Name01

as the name of the name.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Dave Peterson]

Another way via code:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
.Names.Add Name:="MyRng", RefersTo:=.Range("a1:b9")
End With
Next wks
End Sub