Excel 2002

When copying worksheets from one workbook to another, is there a way to
prevent defined names from going with it?

The problem I have, *ALL* defined names are being copied from the source
workbook and into the destination workbook with all of the copied defined
names refering to the source workbook. I do not like this as it can lead up
to some very ill issues given there's a soft code limit of 32,768 defined
names (workbook with at least this many defined names may become unstable)
and a hard code limit of 65,536 (any workbook exceeding this limit, saved,
closed out with the number of defined names in excess of this limit, when
the workbook is opened, it goes into repair mode and everything except for
data and formulas are lost including all formats outside of default format).
Having defined names refering to a workbook that is not the same workbook as
the workbook it's in does me no good for what I'm doing.

NOTE: These limits are NOT documented in the Excel Spec help file, but
rather I ran into these issues a while back when corporate caused some
issues that broke my VBA codes, thus VBA codes needed to be adjusted to
automatically know where to go to when rows/columns are inserted/deleted,
thus using defined names was the only way around the issue that I could
think of. Initially, I was going to have 6 digit figure of the number of
defined names within a single workbook, but quickly did I discover this
issue with defined names, so I had to come up with a compromise, and I have
since come up with a compromise, but still a significant number of defined
names within a single workbook.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Why not build an array that contains all the data from the source sheet and
then go to the destination, insert a new sheet and populate all the cells
based on the array. Not sure how big an array can actually be, but if you
bumped into a problem, you could break your "copying" into segments, read
part, populate part, read more, populate more, etc. Then you'd get no names.

I've looked into this before and didn't find a simple 'cover all
possibilities' type solution, it's a pain. Any approach really needs to be
designed to fit the individual scenario. That may involve various
operations, eg - don't copy the sheet but add a new sheet and -
rngDest.Formulas = rngSource.Formulas
then pastespecial formats (doesn't do row & col dim's).

Then do a SpecialCells error-cells in which to find in particular #Name!
errors; parse the formulas looking for 'names' and replace with the relevant
Refersto from the source wb.

Formulas that refer to other sheets are different; if the destination wb
already contains similar then simply break links.

The above barely scratches the surface of things to consider.

Not sure I agree with all the details you cite, eg I don't recall ALL Names
being copied but rather all Names that refer to cells on the copied sheet,
together with Names used in formulas that refer to other sheets, but not
other Names that refer to other sheets that are otherwise unused on the
copied sheet. It's also slightly different with Named formulas.

Also not sure about those limits either, don't think they are anything like
as specific as you quote. Rather, as quoted by MS, limited by resources
though I think you'd need a powerful system to handle 32k let alone 64k
names.

Regards,
Peter T



"Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Excel 2002
>
> When copying worksheets from one workbook to another, is there a way to
> prevent defined names from going with it?
>
> The problem I have, *ALL* defined names are being copied from the source
> workbook and into the destination workbook with all of the copied defined
> names refering to the source workbook. I do not like this as it can lead
up
> to some very ill issues given there's a soft code limit of 32,768 defined
> names (workbook with at least this many defined names may become unstable)
> and a hard code limit of 65,536 (any workbook exceeding this limit, saved,
> closed out with the number of defined names in excess of this limit, when
> the workbook is opened, it goes into repair mode and everything except for
> data and formulas are lost including all formats outside of default
format).
> Having defined names refering to a workbook that is not the same workbook
as
> the workbook it's in does me no good for what I'm doing.
>
> NOTE: These limits are NOT documented in the Excel Spec help file, but
> rather I ran into these issues a while back when corporate caused some
> issues that broke my VBA codes, thus VBA codes needed to be adjusted to
> automatically know where to go to when rows/columns are inserted/deleted,
> thus using defined names was the only way around the issue that I could
> think of. Initially, I was going to have 6 digit figure of the number of
> defined names within a single workbook, but quickly did I discover this
> issue with defined names, so I had to come up with a compromise, and I
have
> since come up with a compromise, but still a significant number of defined
> names within a single workbook.
>
> --
> Thanks,
>
> Ronald R. Dodge, Jr.
> Production Statistician
> Master MOUS 2000
>
>

Well I had copied the worksheet with the charts on it into another workbook,
which the worksheet with the charts had very few defined names refering to
it. However, since I setup all of the names to be workbook level with maybe
a minor few exceptions, all of the global level names did transfer over. I
can't speak for worksheet level names as I rarely use them. Every single
workbook level range name did transfer over though, and I don't like that.
I had to setup a VBA code to look for and delete all such range names from
the destination workbook.

Here's the code I ended up using for removing such names:

Sub RemoveExternalReferencedNames()
Dim l_lngNumberOfNames As Long, l_lngCurrentNameNumber As Long
l_lngNumberOfNames = Workbooks("ProdReportExec.xls").Names.Count
l_lngCurrentNameNumber = 1
Do While l_lngNumberOfNames >= l_lngCurrentNameNumber
If VBA.InStr(1,
Workbooks(ProdReportExec.xls").Names(l_lngCurrentNameNumber).RefersTo,
".xls", vbTextCompare) > 0 Then
Workbooks("ProdReportExec.xls").Names(l_lngCurrentNameNumber).Delete
l_lngNumberOfNames = l_lngNumberOfNames - 1
Else
l_lngCurrentNameNumber = l_lngCurrentNameNumber + 1
End If
Loop
End Sub

The more and more I work with Excel, the more and more I been moving things
from formulas to VBA codes (which I'm now using class modules extensively).
Set aside from other issues that I have had with using formulas, one of the
other big reasons why I'm moving things to VBA side is to centralized the
processing methods so as if a change is done, it's done in just one
location, not in multiple locations. That's just one of the reasons why
class modules has come into play.

The general trends of the reporting system:

Data processing moving from individual files to a centralized file with the
data for the most part remaining in individual files (after only the data
needed has been pulled from the main database). VBA is now even controlling
some of the charts directly, such as the set of charts with one chart laid
over the top of another chart so as the goal line stretches across the
entire chart (the chart on the bottom) and the actual date line chart draws
the line from point to point and 0's in the chart are treated as
interpolated with the Y value Axis being adjusted by VBA codes to fit the
needs as needed (the chart on the top), so as to give the visual effect of
it being just one chart though in actuality, it's 2 charts to make up that
one chart apparence.

Number crunching is generally moving from formulas to VBA.

As code modulation takes place, more of them are being moved from standard
modules to class modules.

Enumerations are being used significantly more along with the use of
Collections to help in the aid of code modulation, but this doesn't get me
away from the use of defined names cause of the dependency of worksheets.
With this, I also have to keep in mind not only of code cleaning process,
but also of the fact VBA side can only handle up to 64MB of memory usage
within a single VBAProject according to documentations that I have read in
the past.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> I've looked into this before and didn't find a simple 'cover all
> possibilities' type solution, it's a pain. Any approach really needs to be
> designed to fit the individual scenario. That may involve various
> operations, eg - don't copy the sheet but add a new sheet and -
> rngDest.Formulas = rngSource.Formulas
> then pastespecial formats (doesn't do row & col dim's).
>
> Then do a SpecialCells error-cells in which to find in particular #Name!
> errors; parse the formulas looking for 'names' and replace with the
> relevant
> Refersto from the source wb.
>
> Formulas that refer to other sheets are different; if the destination wb
> already contains similar then simply break links.
>
> The above barely scratches the surface of things to consider.
>
> Not sure I agree with all the details you cite, eg I don't recall ALL
> Names
> being copied but rather all Names that refer to cells on the copied sheet,
> together with Names used in formulas that refer to other sheets, but not
> other Names that refer to other sheets that are otherwise unused on the
> copied sheet. It's also slightly different with Named formulas.
>
> Also not sure about those limits either, don't think they are anything
> like
> as specific as you quote. Rather, as quoted by MS, limited by resources
> though I think you'd need a powerful system to handle 32k let alone 64k
> names.
>
> Regards,
> Peter T
>
>
>
> "Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Excel 2002
>>
>> When copying worksheets from one workbook to another, is there a way to
>> prevent defined names from going with it?
>>
>> The problem I have, *ALL* defined names are being copied from the source
>> workbook and into the destination workbook with all of the copied defined
>> names refering to the source workbook. I do not like this as it can lead
> up
>> to some very ill issues given there's a soft code limit of 32,768 defined
>> names (workbook with at least this many defined names may become
>> unstable)
>> and a hard code limit of 65,536 (any workbook exceeding this limit,
>> saved,
>> closed out with the number of defined names in excess of this limit, when
>> the workbook is opened, it goes into repair mode and everything except
>> for
>> data and formulas are lost including all formats outside of default
> format).
>> Having defined names refering to a workbook that is not the same workbook
> as
>> the workbook it's in does me no good for what I'm doing.
>>
>> NOTE: These limits are NOT documented in the Excel Spec help file, but
>> rather I ran into these issues a while back when corporate caused some
>> issues that broke my VBA codes, thus VBA codes needed to be adjusted to
>> automatically know where to go to when rows/columns are inserted/deleted,
>> thus using defined names was the only way around the issue that I could
>> think of. Initially, I was going to have 6 digit figure of the number of
>> defined names within a single workbook, but quickly did I discover this
>> issue with defined names, so I had to come up with a compromise, and I
> have
>> since come up with a compromise, but still a significant number of
>> defined
>> names within a single workbook.
>>
>> --
>> Thanks,
>>
>> Ronald R. Dodge, Jr.
>> Production Statistician
>> Master MOUS 2000
>>
>>
>
>
>

Actually, the worksheets I'm planning on copying from a centralized file
(where all of the data are gathered and processed) are the ones involving
charts. Rather than using arrays as arrays are kinda a generic form with
restrictions and can also be inefficient for certain cases, I been using
class modules, enumerations, and collections instead.

Some might as why not use "Types". Well with the onset of VB.NET, Types are
not allowed and if there comes a time when the code needs to be transferred
to a VB.NET environment, like to spend as little time converting as
possible. That's just one of the restricts to the .NET environment as
compared to VB6 programming environment that MS Office 2000 - MS Office 2003
(can't speak for MS Office 2007) uses a subset of for it's VBA programming
environment.

As for defined names, I would still be dependent on defined names for range
references within VBA as data from our main database is brought into Excel
on worksheets via a 3rd party program, which then VBA takes over from there
to manipulate the data and put into report format. There are many things
that I have done via SQL first, but just as there are restrictions and
limitations to VBA, there's also restrictions and limitations to using SQL
as SQL can't do certain things.

These are codes that's ran nightly and completed before I even come into
work in the morning.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Mike H." <(E-Mail Removed)> wrote in message
news:74800C5B-8D21-4D1C-93A8-(E-Mail Removed)...
> Why not build an array that contains all the data from the source sheet
> and
> then go to the destination, insert a new sheet and populate all the cells
> based on the array. Not sure how big an array can actually be, but if you
> bumped into a problem, you could break your "copying" into segments, read
> part, populate part, read more, populate more, etc. Then you'd get no
> names.
>

Ah, charts with names is yet another issue. There is another way to deal
with copying a sheet with charts + names into another wb without the names,
but it's a long story and ideally needs the help of an app that I just
happen to have.

More generalised, as I mentioned previously copying a sheet to another sheet
doesn't necessarily copy all names in the wb. And depending on the names
quite a lot can be done to reduce those that are copied over. If none are
used in cells then all range names can be removed. If interested try the
following -

Add loads of names that refer to cells on different sheets.
On the first sheet, use some but not all the names as refer to the different
sheets, eg
=NamedCellOnSheet1
=NamedCellOnSheet2
(but don't use all the names on Sheet1)

Sub test()
Dim sOrigShtName As String
Dim nm As Name
Dim wsSource As Worksheet, wsTmp As Worksheet

Set wsSource = ActiveWorkbook.Worksheets(1)
sOrigName = wsSource.Name
wsSource.Name = "Orig"

wsSource.Names.Add "someName", wsSource.Range("a10")

With wsSource.Parent
wsSource.Copy before:=.Sheets(wsSource.Index)
End With

Set wsTmp = ActiveSheet
wsTmp.Name = sOrigName

For Each nm In wsTmp.Names
nm.Delete
Next

wsTmp.Move ' to a new one sht wb for testing

wsSource.Name = sOrigName

Set nms = ActiveWorkbook.Names
For Each nm In nms
Set rng = Nothing
On Error Resume Next
Set rng = nm.RefersToRange
On Error GoTo 0
If Not rng Is Nothing Then

If Not rng.Parent.Parent Is ActiveWorkbook Then
On Error Resume Next
Set ws = Nothing

If rng.Parent.Name = sOrigName Then
Set ws = ActiveSheet
Else
Set ws = Worksheets(rng.Parent.Name)
End If

On Error Resume Next
If Not ws Is Nothing Then
nms.Add nm.Name, ws.Range(rng.Address)
End If
End If
End If
Next

MsgBox ActiveWorkbook.Names.Count

End Sub

compare names in the respective wb's

Regards,
Peter T



"Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Well I had copied the worksheet with the charts on it into another
workbook,
> which the worksheet with the charts had very few defined names refering to
> it. However, since I setup all of the names to be workbook level with
maybe
> a minor few exceptions, all of the global level names did transfer over.
I
> can't speak for worksheet level names as I rarely use them. Every single
> workbook level range name did transfer over though, and I don't like that.
> I had to setup a VBA code to look for and delete all such range names from
> the destination workbook.
>
> Here's the code I ended up using for removing such names:
>
> Sub RemoveExternalReferencedNames()
> Dim l_lngNumberOfNames As Long, l_lngCurrentNameNumber As Long
> l_lngNumberOfNames = Workbooks("ProdReportExec.xls").Names.Count
> l_lngCurrentNameNumber = 1
> Do While l_lngNumberOfNames >= l_lngCurrentNameNumber
> If VBA.InStr(1,
> Workbooks(ProdReportExec.xls").Names(l_lngCurrentNameNumber).RefersTo,
> ".xls", vbTextCompare) > 0 Then
>
Workbooks("ProdReportExec.xls").Names(l_lngCurrentNameNumber).Delete
> l_lngNumberOfNames = l_lngNumberOfNames - 1
> Else
> l_lngCurrentNameNumber = l_lngCurrentNameNumber + 1
> End If
> Loop
> End Sub
>
> The more and more I work with Excel, the more and more I been moving
things
> from formulas to VBA codes (which I'm now using class modules
extensively).
> Set aside from other issues that I have had with using formulas, one of
the
> other big reasons why I'm moving things to VBA side is to centralized the
> processing methods so as if a change is done, it's done in just one
> location, not in multiple locations. That's just one of the reasons why
> class modules has come into play.
>
> The general trends of the reporting system:
>
> Data processing moving from individual files to a centralized file with
the
> data for the most part remaining in individual files (after only the data
> needed has been pulled from the main database). VBA is now even
controlling
> some of the charts directly, such as the set of charts with one chart laid
> over the top of another chart so as the goal line stretches across the
> entire chart (the chart on the bottom) and the actual date line chart
draws
> the line from point to point and 0's in the chart are treated as
> interpolated with the Y value Axis being adjusted by VBA codes to fit the
> needs as needed (the chart on the top), so as to give the visual effect of
> it being just one chart though in actuality, it's 2 charts to make up that
> one chart apparence.
>
> Number crunching is generally moving from formulas to VBA.
>
> As code modulation takes place, more of them are being moved from standard
> modules to class modules.
>
> Enumerations are being used significantly more along with the use of
> Collections to help in the aid of code modulation, but this doesn't get me
> away from the use of defined names cause of the dependency of worksheets.
> With this, I also have to keep in mind not only of code cleaning process,
> but also of the fact VBA side can only handle up to 64MB of memory usage
> within a single VBAProject according to documentations that I have read in
> the past.
>
> --
> Thanks,
>
> Ronald R. Dodge, Jr.
> Production Statistician
> Master MOUS 2000
> "Peter T" <peter_t@discussions> wrote in message
> news:%(E-Mail Removed)...
> > I've looked into this before and didn't find a simple 'cover all
> > possibilities' type solution, it's a pain. Any approach really needs to
be
> > designed to fit the individual scenario. That may involve various
> > operations, eg - don't copy the sheet but add a new sheet and -
> > rngDest.Formulas = rngSource.Formulas
> > then pastespecial formats (doesn't do row & col dim's).
> >
> > Then do a SpecialCells error-cells in which to find in particular #Name!
> > errors; parse the formulas looking for 'names' and replace with the
> > relevant
> > Refersto from the source wb.
> >
> > Formulas that refer to other sheets are different; if the destination wb
> > already contains similar then simply break links.
> >
> > The above barely scratches the surface of things to consider.
> >
> > Not sure I agree with all the details you cite, eg I don't recall ALL
> > Names
> > being copied but rather all Names that refer to cells on the copied
sheet,
> > together with Names used in formulas that refer to other sheets, but not
> > other Names that refer to other sheets that are otherwise unused on the
> > copied sheet. It's also slightly different with Named formulas.
> >
> > Also not sure about those limits either, don't think they are anything
> > like
> > as specific as you quote. Rather, as quoted by MS, limited by resources
> > though I think you'd need a powerful system to handle 32k let alone 64k
> > names.
> >
> > Regards,
> > Peter T
> >
> >
> >
> > "Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Excel 2002
> >>
> >> When copying worksheets from one workbook to another, is there a way to
> >> prevent defined names from going with it?
> >>
> >> The problem I have, *ALL* defined names are being copied from the
source
> >> workbook and into the destination workbook with all of the copied
defined
> >> names refering to the source workbook. I do not like this as it can
lead
> > up
> >> to some very ill issues given there's a soft code limit of 32,768
defined
> >> names (workbook with at least this many defined names may become
> >> unstable)
> >> and a hard code limit of 65,536 (any workbook exceeding this limit,
> >> saved,
> >> closed out with the number of defined names in excess of this limit,
when
> >> the workbook is opened, it goes into repair mode and everything except
> >> for
> >> data and formulas are lost including all formats outside of default
> > format).
> >> Having defined names refering to a workbook that is not the same
workbook
> > as
> >> the workbook it's in does me no good for what I'm doing.
> >>
> >> NOTE: These limits are NOT documented in the Excel Spec help file, but
> >> rather I ran into these issues a while back when corporate caused some
> >> issues that broke my VBA codes, thus VBA codes needed to be adjusted to
> >> automatically know where to go to when rows/columns are
inserted/deleted,
> >> thus using defined names was the only way around the issue that I could
> >> think of. Initially, I was going to have 6 digit figure of the number
of
> >> defined names within a single workbook, but quickly did I discover this
> >> issue with defined names, so I had to come up with a compromise, and I
> > have
> >> since come up with a compromise, but still a significant number of
> >> defined
> >> names within a single workbook.
> >>
> >> --
> >> Thanks,
> >>
> >> Ronald R. Dodge, Jr.
> >> Production Statistician
> >> Master MOUS 2000
> >>
> >>
> >
> >
> >
>
>

As for the limitation I stated about the 65536 range names, why don't you
try to create within a single workbook more than 65536 range names, save it,
close it out, and then open it back up. When you first create a workbook
with more than 65536 range names in it, you can still work with the workbook
for as long as you keep it open, but in the end, once you close out the
workbook with more than that many range names saved in it, when you open it
back up, the workbook does go into repair mode and you lose everything
except for the data, formulas and the default format. If you don't believe
me, try it yourself. Another person didn't believe me, until he tried it
and then confirmed it himself. His initial statement was that he created
74k range names, and worked fine with it. I replied back and asked him did
he save it, close it out and then open it back up. He replied back stating
he initially didn't do that, but when he did do it, he got the same thing as
I got.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> I've looked into this before and didn't find a simple 'cover all
> possibilities' type solution, it's a pain. Any approach really needs to be
> designed to fit the individual scenario. That may involve various
> operations, eg - don't copy the sheet but add a new sheet and -
> rngDest.Formulas = rngSource.Formulas
> then pastespecial formats (doesn't do row & col dim's).
>
> Then do a SpecialCells error-cells in which to find in particular #Name!
> errors; parse the formulas looking for 'names' and replace with the
> relevant
> Refersto from the source wb.
>
> Formulas that refer to other sheets are different; if the destination wb
> already contains similar then simply break links.
>
> The above barely scratches the surface of things to consider.
>
> Not sure I agree with all the details you cite, eg I don't recall ALL
> Names
> being copied but rather all Names that refer to cells on the copied sheet,
> together with Names used in formulas that refer to other sheets, but not
> other Names that refer to other sheets that are otherwise unused on the
> copied sheet. It's also slightly different with Named formulas.
>
> Also not sure about those limits either, don't think they are anything
> like
> as specific as you quote. Rather, as quoted by MS, limited by resources
> though I think you'd need a powerful system to handle 32k let alone 64k
> names.
>
> Regards,
> Peter T
>
>
>
> "Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Excel 2002
>>
>> When copying worksheets from one workbook to another, is there a way to
>> prevent defined names from going with it?
>>
>> The problem I have, *ALL* defined names are being copied from the source
>> workbook and into the destination workbook with all of the copied defined
>> names refering to the source workbook. I do not like this as it can lead
> up
>> to some very ill issues given there's a soft code limit of 32,768 defined
>> names (workbook with at least this many defined names may become
>> unstable)
>> and a hard code limit of 65,536 (any workbook exceeding this limit,
>> saved,
>> closed out with the number of defined names in excess of this limit, when
>> the workbook is opened, it goes into repair mode and everything except
>> for
>> data and formulas are lost including all formats outside of default
> format).
>> Having defined names refering to a workbook that is not the same workbook
> as
>> the workbook it's in does me no good for what I'm doing.
>>
>> NOTE: These limits are NOT documented in the Excel Spec help file, but
>> rather I ran into these issues a while back when corporate caused some
>> issues that broke my VBA codes, thus VBA codes needed to be adjusted to
>> automatically know where to go to when rows/columns are inserted/deleted,
>> thus using defined names was the only way around the issue that I could
>> think of. Initially, I was going to have 6 digit figure of the number of
>> defined names within a single workbook, but quickly did I discover this
>> issue with defined names, so I had to come up with a compromise, and I
> have
>> since come up with a compromise, but still a significant number of
>> defined
>> names within a single workbook.
>>
>> --
>> Thanks,
>>
>> Ronald R. Dodge, Jr.
>> Production Statistician
>> Master MOUS 2000
>>
>>
>
>
>

64k names may well be an absolute limit and in a way it makes sense. I may
have expressed my doubt the wrong way inasmuch as for all practical
purposes, in most systems, I would expect a "reasonably usable" limit to be
much less than that.

Regards,
Peter T

"Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
news:e%(E-Mail Removed)...
> As for the limitation I stated about the 65536 range names, why don't you
> try to create within a single workbook more than 65536 range names, save
it,
> close it out, and then open it back up. When you first create a workbook
> with more than 65536 range names in it, you can still work with the
workbook
> for as long as you keep it open, but in the end, once you close out the
> workbook with more than that many range names saved in it, when you open
it
> back up, the workbook does go into repair mode and you lose everything
> except for the data, formulas and the default format. If you don't
believe
> me, try it yourself. Another person didn't believe me, until he tried it
> and then confirmed it himself. His initial statement was that he created
> 74k range names, and worked fine with it. I replied back and asked him
did
> he save it, close it out and then open it back up. He replied back
stating
> he initially didn't do that, but when he did do it, he got the same thing
as
> I got.
>
> --
> Thanks,
>
> Ronald R. Dodge, Jr.
> Production Statistician
> Master MOUS 2000
> "Peter T" <peter_t@discussions> wrote in message
> news:%(E-Mail Removed)...
> > I've looked into this before and didn't find a simple 'cover all
> > possibilities' type solution, it's a pain. Any approach really needs to
be
> > designed to fit the individual scenario. That may involve various
> > operations, eg - don't copy the sheet but add a new sheet and -
> > rngDest.Formulas = rngSource.Formulas
> > then pastespecial formats (doesn't do row & col dim's).
> >
> > Then do a SpecialCells error-cells in which to find in particular #Name!
> > errors; parse the formulas looking for 'names' and replace with the
> > relevant
> > Refersto from the source wb.
> >
> > Formulas that refer to other sheets are different; if the destination wb
> > already contains similar then simply break links.
> >
> > The above barely scratches the surface of things to consider.
> >
> > Not sure I agree with all the details you cite, eg I don't recall ALL
> > Names
> > being copied but rather all Names that refer to cells on the copied
sheet,
> > together with Names used in formulas that refer to other sheets, but not
> > other Names that refer to other sheets that are otherwise unused on the
> > copied sheet. It's also slightly different with Named formulas.
> >
> > Also not sure about those limits either, don't think they are anything
> > like
> > as specific as you quote. Rather, as quoted by MS, limited by resources
> > though I think you'd need a powerful system to handle 32k let alone 64k
> > names.
> >
> > Regards,
> > Peter T
> >
> >
> >
> > "Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Excel 2002
> >>
> >> When copying worksheets from one workbook to another, is there a way to
> >> prevent defined names from going with it?
> >>
> >> The problem I have, *ALL* defined names are being copied from the
source
> >> workbook and into the destination workbook with all of the copied
defined
> >> names refering to the source workbook. I do not like this as it can
lead
> > up
> >> to some very ill issues given there's a soft code limit of 32,768
defined
> >> names (workbook with at least this many defined names may become
> >> unstable)
> >> and a hard code limit of 65,536 (any workbook exceeding this limit,
> >> saved,
> >> closed out with the number of defined names in excess of this limit,
when
> >> the workbook is opened, it goes into repair mode and everything except
> >> for
> >> data and formulas are lost including all formats outside of default
> > format).
> >> Having defined names refering to a workbook that is not the same
workbook
> > as
> >> the workbook it's in does me no good for what I'm doing.
> >>
> >> NOTE: These limits are NOT documented in the Excel Spec help file, but
> >> rather I ran into these issues a while back when corporate caused some
> >> issues that broke my VBA codes, thus VBA codes needed to be adjusted to
> >> automatically know where to go to when rows/columns are
inserted/deleted,
> >> thus using defined names was the only way around the issue that I could
> >> think of. Initially, I was going to have 6 digit figure of the number
of
> >> defined names within a single workbook, but quickly did I discover this
> >> issue with defined names, so I had to come up with a compromise, and I
> > have
> >> since come up with a compromise, but still a significant number of
> >> defined
> >> names within a single workbook.
> >>
> >> --
> >> Thanks,
> >>
> >> Ronald R. Dodge, Jr.
> >> Production Statistician
> >> Master MOUS 2000
> >>
> >>
> >
> >
> >
>
>

Well in many respects, though I was going to use each cell as a range name
as a cross point between period number and field, but due to the hard code
limit, that obviously didn't pan out. I have since used individual columns
and individual rows as range names. In some sense, I have been able to get
away from having to use it on every row, but still stuck with using it on
every column. This is simply cause if a new column is inserted at some
point of time, I don't want to have to go back and modify all of my impacted
VBA codes for such ranges, as such VBA codes could be overlooked, not to
mention potentially the amount of time it could take to make such
adjustments.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> 64k names may well be an absolute limit and in a way it makes sense. I may
> have expressed my doubt the wrong way inasmuch as for all practical
> purposes, in most systems, I would expect a "reasonably usable" limit to
> be
> much less than that.
>
> Regards,
> Peter T
>
> "Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
> news:e%(E-Mail Removed)...
>> As for the limitation I stated about the 65536 range names, why don't you
>> try to create within a single workbook more than 65536 range names, save
> it,
>> close it out, and then open it back up. When you first create a workbook
>> with more than 65536 range names in it, you can still work with the
> workbook
>> for as long as you keep it open, but in the end, once you close out the
>> workbook with more than that many range names saved in it, when you open
> it
>> back up, the workbook does go into repair mode and you lose everything
>> except for the data, formulas and the default format. If you don't
> believe
>> me, try it yourself. Another person didn't believe me, until he tried it
>> and then confirmed it himself. His initial statement was that he created
>> 74k range names, and worked fine with it. I replied back and asked him
> did
>> he save it, close it out and then open it back up. He replied back
> stating
>> he initially didn't do that, but when he did do it, he got the same thing
> as
>> I got.
>>
>> --
>> Thanks,
>>
>> Ronald R. Dodge, Jr.
>> Production Statistician
>> Master MOUS 2000
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%(E-Mail Removed)...
>> > I've looked into this before and didn't find a simple 'cover all
>> > possibilities' type solution, it's a pain. Any approach really needs to
> be
>> > designed to fit the individual scenario. That may involve various
>> > operations, eg - don't copy the sheet but add a new sheet and -
>> > rngDest.Formulas = rngSource.Formulas
>> > then pastespecial formats (doesn't do row & col dim's).
>> >
>> > Then do a SpecialCells error-cells in which to find in particular
>> > #Name!
>> > errors; parse the formulas looking for 'names' and replace with the
>> > relevant
>> > Refersto from the source wb.
>> >
>> > Formulas that refer to other sheets are different; if the destination
>> > wb
>> > already contains similar then simply break links.
>> >
>> > The above barely scratches the surface of things to consider.
>> >
>> > Not sure I agree with all the details you cite, eg I don't recall ALL
>> > Names
>> > being copied but rather all Names that refer to cells on the copied
> sheet,
>> > together with Names used in formulas that refer to other sheets, but
>> > not
>> > other Names that refer to other sheets that are otherwise unused on the
>> > copied sheet. It's also slightly different with Named formulas.
>> >
>> > Also not sure about those limits either, don't think they are anything
>> > like
>> > as specific as you quote. Rather, as quoted by MS, limited by resources
>> > though I think you'd need a powerful system to handle 32k let alone 64k
>> > names.
>> >
>> > Regards,
>> > Peter T
>> >
>> >
>> >
>> > "Ronald R. Dodge, Jr." <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> Excel 2002
>> >>
>> >> When copying worksheets from one workbook to another, is there a way
>> >> to
>> >> prevent defined names from going with it?
>> >>
>> >> The problem I have, *ALL* defined names are being copied from the
> source
>> >> workbook and into the destination workbook with all of the copied
> defined
>> >> names refering to the source workbook. I do not like this as it can
> lead
>> > up
>> >> to some very ill issues given there's a soft code limit of 32,768
> defined
>> >> names (workbook with at least this many defined names may become
>> >> unstable)
>> >> and a hard code limit of 65,536 (any workbook exceeding this limit,
>> >> saved,
>> >> closed out with the number of defined names in excess of this limit,
> when
>> >> the workbook is opened, it goes into repair mode and everything except
>> >> for
>> >> data and formulas are lost including all formats outside of default
>> > format).
>> >> Having defined names refering to a workbook that is not the same
> workbook
>> > as
>> >> the workbook it's in does me no good for what I'm doing.
>> >>
>> >> NOTE: These limits are NOT documented in the Excel Spec help file,
>> >> but
>> >> rather I ran into these issues a while back when corporate caused some
>> >> issues that broke my VBA codes, thus VBA codes needed to be adjusted
>> >> to
>> >> automatically know where to go to when rows/columns are
> inserted/deleted,
>> >> thus using defined names was the only way around the issue that I
>> >> could
>> >> think of. Initially, I was going to have 6 digit figure of the number
> of
>> >> defined names within a single workbook, but quickly did I discover
>> >> this
>> >> issue with defined names, so I had to come up with a compromise, and I
>> > have
>> >> since come up with a compromise, but still a significant number of
>> >> defined
>> >> names within a single workbook.
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Ronald R. Dodge, Jr.
>> >> Production Statistician
>> >> Master MOUS 2000
>> >>
>> >>
>> >
>> >
>> >
>>
>>
>
>