Copying tabs in a workbook

  • Thread starter Thread starter Kevin Daly
  • Start date Start date
K

Kevin Daly

I have a template worksheet that I need to duplicate 35
times. I then change the names of each tab and populate
the tabs with data.

I was using the following statement
for i=1 to 20
Sheets("template").Copy Before:=Sheets(1)
next
and things worked ok until I began creating too many new
worksheets. It seems like there is a problem when
creating somewhere between 25 and 30 new tabs.

Has anyone run into this kind of problem? I've also tried
using the FillAcrossSheets worksheet function, but that
seems to fail when I try to fill more than 29 new sheets.

Thanks
 
I've just created 120, no problems (Excel 2000, XP).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
If you are using Excel 97, this can be caused by a problem with the code
name. Go into the VBE. In the project explorer, If you have names like

Sheet1111111111111111111111111

then this is the cause. This code name can not be longer than 31 or 32
characters I don't believe.
 
I also have a lot of formatting as well as 6 charts that are included in
the tab. When I remove the charts, I don't have any problems copying.

Does anyone know of any restrictions around creating multiple charts in
multiple tabs?
 
In xl97, charts could create memory problems. So if removing them allows
the copying, then this is probably the source of the problem. There is no
absolute limit published (such as you can have no more than 40 charts as an
example).
 
I'm using Excel 2000, so it seems like there is still a memory issue
with charts in this version.

Are there any workarounds or would a newer version of Excel eliminate
this problem?
 
I can't say. I don't recall that there is an issue with charts in xl2000.
Are your sheets zoomed. If so, try putting them at 100%.

The memory problem had to do with font scaling as I recall, so perhaps it
has something to do with that. But I know there was another type of memory
problem that had to do with objects on sheets and sheet being zoomed at
other than 100%.

Later versions - I can't say, since we don't know specifically what the
cause is in your case. (and I personally couldn't say if I did unless it
was a known problem that has been fixed).
 
Back
Top