Name range visability

[matts2]

I am programatically creating a workbook. I copy some worksheets over
and need to give a name to a range of cells so another sheet can
reference them. I used this code:

Names.Add Name:=RangeName, RefersTo:=ReferString, Visible:=True

The name appears just find in the sheet named, but the other sheets
don't seem to know about the named range. The cells show the #REF!
error and the name box is empty. I tried "Thisworkbook.Names", but got
the same result. How can I make the names visable to the other
worksheets?

TIA.

 

[matts2]

I am programatically creating a workbook. I copy some worksheets over
and need to give a name to a range of cells so another sheet can
reference them. I used this code:

Names.Add Name:=RangeName, RefersTo:=ReferString, Visible:=True

The name appears just find in the sheet named, but the other sheets
don't seem to know about the named range. The cells show the #REF!
error and the name box is empty. I tried "Thisworkbook.Names", but got
the same result. How can I make the names visable to the other
worksheets?

TIA.

Also, how do I make it so that the copied sheets refer to the new
workbook. That is, I have OldWB.Sheet1 and OldWB.Sheet2. I copy both to
NewWB. I want the cell in NewWB.Sheet2 to refer to NewWBSheet1, but it
refers to OldWB.Sheet1.

(Sorry if this is obivous stuff, but I am not an Excel or vba
programmer.)

Again, TIA.