Names Problems

[Paul Smith]

I have skirted around describing my actual issue because I thought I could
solve it myself if I understood how Named ranges work in Excel, but it has
defeated me. I would like to thank all the people who have answered my
previous Name related questions this evening.

My actual problem......

I have an XLA which contains two worksheets. On the first is static
information contained in workbook level named ranges. The second Worksheets
is a pseudo template which has formulas based on the workbook level named
ranges on the first sheet - these are used to make the formulae readable.
It also contains worksheets level named ranges. These need to be worksheet
level ones because as numerous copies are made of this 'template' into a new
workbook, each range on each sheet needs to be able to be referenced.

Code, from within the XLA, first creates a new workbook by copying the
static sheet to a new workbook. This works fine as the workbook level names
on the copied worksheet refer to the new workbook.

Code then takes a copy the 'template' sheet in the XLA, and copies it into
the new workbook. This procedure, because the formula contain references to
the XLA static sheet, copies worksheet level versions of all the workbook
level names into the new workbook. It also creates the required worksheet
level names as required. The problem is the formulas now look correct, they
refer to named ranges, but the actual references of the named ranges refer
back to the Static sheet in the XLA.

What I need to do is delete all the erroneously created worksheet level
versions of the workbook level names. This would level just the correct
versions which were created when the static sheet was copied.

If anyone knowledgeable about this kind of problem would care to suggest a
solution as to how I can stop the issues I am having with named ranges
please post help me [smile]

PWS

 

[Bob Phillips]

Paul,

Why not just create the worksheet names dynamically as you copy the template
across?

 

[Paul Smith]

What does this 'create the worksheet names dynamically ' mean?

The act of copying the worksheet from the XLA to the new workbook copies the
names as described below. Of course I could create the new workbook from
scratch, reformatting it, resizing columns etc. but surely the process of
making a copy of a worksheets and copying it into another is the most
efficient way to achieve what I need, other than the names issue.

PWS

Paul,

Why not just create the worksheet names dynamically as you copy the
template
across?

--
HTH

Bob Phillips

I have skirted around describing my actual issue because I thought I
could
solve it myself if I understood how Named ranges work in Excel, but it
has
defeated me. I would like to thank all the people who have answered my
previous Name related questions this evening.

My actual problem......

I have an XLA which contains two worksheets. On the first is static
information contained in workbook level named ranges. The second Worksheets
is a pseudo template which has formulas based on the workbook level named
ranges on the first sheet - these are used to make the formulae readable.
It also contains worksheets level named ranges. These need to be worksheet
level ones because as numerous copies are made of this 'template' into a new
workbook, each range on each sheet needs to be able to be referenced.

Code, from within the XLA, first creates a new workbook by copying the
static sheet to a new workbook. This works fine as the workbook level names
on the copied worksheet refer to the new workbook.

Code then takes a copy the 'template' sheet in the XLA, and copies it
into
the new workbook. This procedure, because the formula contain references to
the XLA static sheet, copies worksheet level versions of all the workbook
level names into the new workbook. It also creates the required
worksheet
level names as required. The problem is the formulas now look correct, they
refer to named ranges, but the actual references of the named ranges
refer
back to the Static sheet in the XLA.

What I need to do is delete all the erroneously created worksheet level
versions of the workbook level names. This would level just the correct
versions which were created when the static sheet was copied.

If anyone knowledgeable about this kind of problem would care to suggest
a
solution as to how I can stop the issues I am having with named ranges
please post help me [smile]

PWS

 

[Bob Phillips]

No I don't mean do all the work from scratch, but as you, presumably, have
code to create the new book, create the names at that point

With Activeworkbook.Worksheets("Sheet1")
.Range("a1:a10").Name = .Name & "!myRange"
End With

--
HTH

Bob Phillips

What does this 'create the worksheet names dynamically ' mean?

The act of copying the worksheet from the XLA to the new workbook copies the
names as described below. Of course I could create the new workbook from
scratch, reformatting it, resizing columns etc. but surely the process of
making a copy of a worksheets and copying it into another is the most
efficient way to achieve what I need, other than the names issue.

PWS

Paul,

Why not just create the worksheet names dynamically as you copy the
template
across?

--
HTH

Bob Phillips

I have skirted around describing my actual issue because I thought I
could
solve it myself if I understood how Named ranges work in Excel, but it
has
defeated me. I would like to thank all the people who have answered my
previous Name related questions this evening.

My actual problem......

I have an XLA which contains two worksheets. On the first is static
information contained in workbook level named ranges. The second Worksheets
is a pseudo template which has formulas based on the workbook level named
ranges on the first sheet - these are used to make the formulae readable.
It also contains worksheets level named ranges. These need to be worksheet
level ones because as numerous copies are made of this 'template' into

a
new

workbook, each range on each sheet needs to be able to be referenced.

Code, from within the XLA, first creates a new workbook by copying the
static sheet to a new workbook. This works fine as the workbook level names
on the copied worksheet refer to the new workbook.

Code then takes a copy the 'template' sheet in the XLA, and copies it
into
the new workbook. This procedure, because the formula contain

references
to

the XLA static sheet, copies worksheet level versions of all the workbook
level names into the new workbook. It also creates the required
worksheet
level names as required. The problem is the formulas now look correct, they
refer to named ranges, but the actual references of the named ranges
refer
back to the Static sheet in the XLA.

What I need to do is delete all the erroneously created worksheet level
versions of the workbook level names. This would level just the correct
versions which were created when the static sheet was copied.

If anyone knowledgeable about this kind of problem would care to suggest
a
solution as to how I can stop the issues I am having with named ranges
please post help me [smile]

PWS

 

[Paul Smith]

Bob,

I took the time to write a quite lengthy description of my problem, and
although you have offered a small degree of alternate advice you have not
come close to solving my problem. Indeed I am not actually sure you have
taken the time to understand it.

I understand that you are aiming to assist people when you answer posts, but
having looked at the brevity of your most of your efforts I wonder if your
apparent willingness to dash of a brief reply is always appropraite. I am
happy to accept that your brief posts offer assistance to most, but not to
me.

I intend to post my question again, because I believe the more serious MVPs
in this forum do not look at posts that have a number of replies. Please
could I ask you not to post a reply to my second request for assistance.

PWS

No I don't mean do all the work from scratch, but as you, presumably, have
code to create the new book, create the names at that point

With Activeworkbook.Worksheets("Sheet1")
.Range("a1:a10").Name = .Name & "!myRange"
End With

--
HTH

Bob Phillips

What does this 'create the worksheet names dynamically ' mean?

The act of copying the worksheet from the XLA to the new workbook copies the
names as described below. Of course I could create the new workbook from
scratch, reformatting it, resizing columns etc. but surely the process of
making a copy of a worksheets and copying it into another is the most
efficient way to achieve what I need, other than the names issue.

PWS

Paul,

Why not just create the worksheet names dynamically as you copy the
template
across?

--
HTH

Bob Phillips

I have skirted around describing my actual issue because I thought I
could
solve it myself if I understood how Named ranges work in Excel, but it
has
defeated me. I would like to thank all the people who have answered
my
previous Name related questions this evening.

My actual problem......

I have an XLA which contains two worksheets. On the first is static
information contained in workbook level named ranges. The second
Worksheets
is a pseudo template which has formulas based on the workbook level named
ranges on the first sheet - these are used to make the formulae readable.
It also contains worksheets level named ranges. These need to be
worksheet
level ones because as numerous copies are made of this 'template' into a
new
workbook, each range on each sheet needs to be able to be referenced.

Code, from within the XLA, first creates a new workbook by copying the
static sheet to a new workbook. This works fine as the workbook level
names
on the copied worksheet refer to the new workbook.

Code then takes a copy the 'template' sheet in the XLA, and copies it
into
the new workbook. This procedure, because the formula contain references
to
the XLA static sheet, copies worksheet level versions of all the workbook
level names into the new workbook. It also creates the required
worksheet
level names as required. The problem is the formulas now look
correct,
they
refer to named ranges, but the actual references of the named ranges
refer
back to the Static sheet in the XLA.

What I need to do is delete all the erroneously created worksheet
level
versions of the workbook level names. This would level just the correct
versions which were created when the static sheet was copied.

If anyone knowledgeable about this kind of problem would care to suggest
a
solution as to how I can stop the issues I am having with named ranges
please post help me [smile]

PWS

 

[Dave Peterson]

You have formulas on the template sheet that refer to named ranges on the static
worksheet?

How about this:
copy the static worksheet (you said that works ok) to the other workbook.

for the template worksheet
change all the formulas to text
replace = (equal sign) with $$$$$= (some unique string)
copy that template worksheet
fix the text formulas by reversing the mass change--in both worksheets.

I have skirted around describing my actual issue because I thought I could
solve it myself if I understood how Named ranges work in Excel, but it has
defeated me. I would like to thank all the people who have answered my
previous Name related questions this evening.

My actual problem......

I have an XLA which contains two worksheets. On the first is static
information contained in workbook level named ranges. The second Worksheets
is a pseudo template which has formulas based on the workbook level named
ranges on the first sheet - these are used to make the formulae readable.
It also contains worksheets level named ranges. These need to be worksheet
level ones because as numerous copies are made of this 'template' into a new
workbook, each range on each sheet needs to be able to be referenced.

Code, from within the XLA, first creates a new workbook by copying the
static sheet to a new workbook. This works fine as the workbook level names
on the copied worksheet refer to the new workbook.

Code then takes a copy the 'template' sheet in the XLA, and copies it into
the new workbook. This procedure, because the formula contain references to
the XLA static sheet, copies worksheet level versions of all the workbook
level names into the new workbook. It also creates the required worksheet
level names as required. The problem is the formulas now look correct, they
refer to named ranges, but the actual references of the named ranges refer
back to the Static sheet in the XLA.

What I need to do is delete all the erroneously created worksheet level
versions of the workbook level names. This would level just the correct
versions which were created when the static sheet was copied.

If anyone knowledgeable about this kind of problem would care to suggest a
solution as to how I can stop the issues I am having with named ranges
please post help me [smile]

PWS