named range problem

G

Glen Mettler

I have 5 sheets in a workbook. Each sheet has a column
named "FunctCostCenter". I want to create a named range
for "FunctCostCenter" on each sheet. I can't seem to get
it to accept "FunctCostCenter" as a name for each sheet -
if I do it manually or programmatically - it overwrites
the previous address for "Notes".

Here is my code that creats the named range:
Sheets(tabname).Range(thisrange).Name = "FunctCostCenter"

When I run it on sheet 1 it creates it for sheet 1. When
I run it on sheet 2 it overwrites the previous and sets
the address for sheet 2.

What have I done wrong?

Thanks,

Glen
 
W

William

Basically uou need to precede the name assigned to the range on each sheet
with the sheet name, so....

Sub test()
Dim ws As Worksheet, x As String
For Each ws In Worksheets
x = "'" & ws.Name & "'!FunctCostCenter"
ThisWorkbook.Names.Add Name:=x, RefersTo:=ws.Range("A:A")
Next ws
End Sub

--
XL2002
Regards

William

[email protected]

| I have 5 sheets in a workbook. Each sheet has a column
| named "FunctCostCenter". I want to create a named range
| for "FunctCostCenter" on each sheet. I can't seem to get
| it to accept "FunctCostCenter" as a name for each sheet -
| if I do it manually or programmatically - it overwrites
| the previous address for "Notes".
|
| Here is my code that creats the named range:
| Sheets(tabname).Range(thisrange).Name = "FunctCostCenter"
|
| When I run it on sheet 1 it creates it for sheet 1. When
| I run it on sheet 2 it overwrites the previous and sets
| the address for sheet 2.
|
| What have I done wrong?
|
| Thanks,
|
| Glen
 
Top