Copying Worksheet Problem - Names

I apologise for reporsting this, but given the previous responses I am
hoping that other people might provide some more relevant insight into my
issue, and suggest a solution.

PWS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

Jim,

The problem is that the second sheets has to be copied multiple times, hence
why the worksheet level names must stay but the duplicated workbook level
names (now duplicated as worksheet level ones) have to be elininated.

PWS


"Jim Rech" <[email protected]> wrote in message
news:[email protected]...
>I don't know if this will short-circuit your problem but I found that when
>I
> used a macro to copy the sheets as you described I duplicated your
> problem:
>
> Sub OneAtATime()
> ThisWorkbook.Worksheets("Sheet1").Copy
> ThisWorkbook.Worksheets("Sheet2").Copy ActiveWorkbook.Worksheets(1)
> End Sub
>
> But if I copied the sheets all at once there was no problem:
>
> Sub AllAtOnce()
> Sheets(Array("Sheet1", "Sheet2")).Copy
> End Sub
>
> FWIW.
>
> --
> Jim
> "Paul Smith" <[email protected]> wrote in message
> news:[email protected]...
> |
> | I apologise for reporsting this, but given the previous responses I am
> | hoping that other people might provide some more relevant insight into
> my
> | issue, and suggest a solution.
> |
> | PWS
> |
> | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> |
> | 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,

Prior to copying the worksheet, change each formula to a string, preceeded by a known precursor
string:

Sub ConvertFormulas()
Dim myCell As Range
For Each myCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
myCell.Value = "XX" & myCell.Formula
Next myCell
End Sub

That will prevent Excel from interpreting the names as ranges.

Then after you copy the sheet, convert the formulas back, at which point Excel will associate the
name with the current workbook, not the source workbook.

Sub TryNow2()
Dim myCell As Range
On Error Resume Next
Application.EnableEvents = False
For Each myCell In ActiveSheet.UsedRange
If Left(myCell.Value, 3) = "XX=" Then
myCell.Formula = Mid(myCell.Value, 3, Len(myCell.Value))
End If
Next myCell
Application.EnableEvents = True

End Sub

How well that works will depend on the number of formulas.

HTH,
Bernie
MS Excel MVP


"Paul W Smith" <[email protected]> wrote in message
news:[email protected]...
> Jim,
>
> The problem is that the second sheets has to be copied multiple times, hence why the worksheet
> level names must stay but the duplicated workbook level names (now duplicated as worksheet level
> ones) have to be elininated.
>
> PWS
>
>
> "Jim Rech" <[email protected]> wrote in message news:[email protected]...
>>I don't know if this will short-circuit your problem but I found that when I
>> used a macro to copy the sheets as you described I duplicated your problem:
>>
>> Sub OneAtATime()
>> ThisWorkbook.Worksheets("Sheet1").Copy
>> ThisWorkbook.Worksheets("Sheet2").Copy ActiveWorkbook.Worksheets(1)
>> End Sub
>>
>> But if I copied the sheets all at once there was no problem:
>>
>> Sub AllAtOnce()
>> Sheets(Array("Sheet1", "Sheet2")).Copy
>> End Sub
>>
>> FWIW.
>>
>> --
>> Jim
>> "Paul Smith" <[email protected]> wrote in message
>> news:[email protected]...
>> |
>> | I apologise for reporsting this, but given the previous responses I am
>> | hoping that other people might provide some more relevant insight into my
>> | issue, and suggest a solution.
>> |
>> | PWS
>> |
>> | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> |
>> | 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 previously suggested a faster way of doing this in a previouls
thread that Paul Smith initiated:

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.

--
Regards,
Tom Ogilvy


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> Paul,
>
> Prior to copying the worksheet, change each formula to a string, preceeded
by a known precursor
> string:
>
> Sub ConvertFormulas()
> Dim myCell As Range
> For Each myCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
> myCell.Value = "XX" & myCell.Formula
> Next myCell
> End Sub
>
> That will prevent Excel from interpreting the names as ranges.
>
> Then after you copy the sheet, convert the formulas back, at which point
Excel will associate the
> name with the current workbook, not the source workbook.
>
> Sub TryNow2()
> Dim myCell As Range
> On Error Resume Next
> Application.EnableEvents = False
> For Each myCell In ActiveSheet.UsedRange
> If Left(myCell.Value, 3) = "XX=" Then
> myCell.Formula = Mid(myCell.Value, 3, Len(myCell.Value))
> End If
> Next myCell
> Application.EnableEvents = True
>
> End Sub
>
> How well that works will depend on the number of formulas.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Paul W Smith" <[email protected]> wrote in message
> news:[email protected]...
> > Jim,
> >
> > The problem is that the second sheets has to be copied multiple times,
hence why the worksheet
> > level names must stay but the duplicated workbook level names (now
duplicated as worksheet level
> > ones) have to be elininated.
> >
> > PWS
> >
> >
> > "Jim Rech" <[email protected]> wrote in message
news:[email protected]...
> >>I don't know if this will short-circuit your problem but I found that
when I
> >> used a macro to copy the sheets as you described I duplicated your
problem:
> >>
> >> Sub OneAtATime()
> >> ThisWorkbook.Worksheets("Sheet1").Copy
> >> ThisWorkbook.Worksheets("Sheet2").Copy ActiveWorkbook.Worksheets(1)
> >> End Sub
> >>
> >> But if I copied the sheets all at once there was no problem:
> >>
> >> Sub AllAtOnce()
> >> Sheets(Array("Sheet1", "Sheet2")).Copy
> >> End Sub
> >>
> >> FWIW.
> >>
> >> --
> >> Jim
> >> "Paul Smith" <[email protected]> wrote in message
> >> news:[email protected]...
> >> |
> >> | I apologise for reporsting this, but given the previous responses I
am
> >> | hoping that other people might provide some more relevant insight
into my
> >> | issue, and suggest a solution.
> >> |
> >> | PWS
> >> |
> >> | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >> |
> >> | 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
> >> |
> >> |
> >>
> >>
> >
> >
>
>

So I would suggest copying the two sheets all at once as in the macro I
posted, and then copy the template sheet in the new workbook multiple times
rather than the one in the add-in.

--
Jim
"Paul W Smith" <[email protected]> wrote in message
news:[email protected]...
| Jim,
|
| The problem is that the second sheets has to be copied multiple times,
hence
| why the worksheet level names must stay but the duplicated workbook level
| names (now duplicated as worksheet level ones) have to be elininated.
|
| PWS
|
|
| "Jim Rech" <[email protected]> wrote in message
| news:[email protected]...
| >I don't know if this will short-circuit your problem but I found that
when
| >I
| > used a macro to copy the sheets as you described I duplicated your
| > problem:
| >
| > Sub OneAtATime()
| > ThisWorkbook.Worksheets("Sheet1").Copy
| > ThisWorkbook.Worksheets("Sheet2").Copy ActiveWorkbook.Worksheets(1)
| > End Sub
| >
| > But if I copied the sheets all at once there was no problem:
| >
| > Sub AllAtOnce()
| > Sheets(Array("Sheet1", "Sheet2")).Copy
| > End Sub
| >
| > FWIW.
| >
| > --
| > Jim
| > "Paul Smith" <[email protected]> wrote in message
| > news:[email protected]...
| > |
| > | I apologise for reporsting this, but given the previous responses I am
| > | hoping that other people might provide some more relevant insight into
| > my
| > | issue, and suggest a solution.
| > |
| > | PWS
| > |
| > | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| > |
| > | 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
| > |
| > |
| >
| >
|
|