Copying Worksheets from one workbook to another

[Ronald R. Dodge, Jr.]

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

 

[Mike H.]

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.

 

[Peter T]

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.]

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

 

[Ronald R. Dodge, Jr.]

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

 

[Peter T]

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.]

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]

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.]

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