VBA - Problems Creating new sheets inside a Excel workbook

[Knutjo]

Inside an existing Excel workbook i have an existing "master" sheet. This sheet I want to copy a number of times inside the workbook.

Every time I reach 60 sheets inside my workbook I get "Subscript out of range (Error 9)".

I have made a smal macro which look like this doing the creation of all the sheets:

Sub AppendSheet()
Worksheets("ANLEGG-Utstyrstype MASTER&quot.Copy after:=Worksheets(Worksheets.Count)
End Sub

Is this some (for me unknown) limitation in VBA/Excel or am I doing something wrong ?

 

[Danyul]

Hi Knutjo,

I'm afraid I haven't solved the problem. However I ran the following code:


Sub AppendSheet()

For i = 1 To 100

Worksheets("ANLEGG-Utstyrstype MASTER&quot.Copy after:=Worksheets(Worksheets.Count)

Next i

End Sub

and it worked fine, I'm using excel 97 on a win 98 platform.

So it doesn't seem to be a VBA limitation.

The only time I could replicate the error you found was if I deleted the original master sheet (or re-named it) then the code can't find 'ANLEGG-Utstyrstype MASTER' as it no longer exists.....

Hope this gives you a clue

Regards

Dan

 

[ilses]

Hi Knutjo,

My hunge is that you run into a problem with the VBA code name of your sheets. These progressively increase to a maximum of some 35 characters or so. Check the project explorer to see the code names.

HTH


ilses

 

[acron]

I suspect ilses is correct, as when you copy a worksheet excel just appends an additionall 1 to the codename, so that you get a string on 1's and eventually they exceed excel's tolerance. A bad design flaw.

What you could do is save the Master Worksheet as Sheet.xlt in your XLStart folder, and then rather than copy the sheet, jus add a new worksheet, which should be based on the master sheet. If you already have a sheet.xlt, you could rename/copy it and then restore it when finished.

Otherwise you will need to change the codenames of the sheets as you copy them, which might be problematic.

AC