limited number of worksheets?

  • Thread starter Thread starter Cade
  • Start date Start date
C

Cade

Hi all,

I have a macro that creates new sheets based on a list of items I
provide. The list is about 20 entries strong - hence 20 worksheets get
created with the name being each entry in the list. Since I work on
this macro, I often delete all the sheets and let it run again - hence
re-creating all the sheets. I noticed that I can do that only a
limited time. After deleting the sheets about 4 or 5 times, the macro
craps out. It must be part of the file because closing and re-opening
Excel and the file doesn't solve the problem.

Any idea how I can reset the "count" or what the problem could be?

Thanks,

Cade
 
Cade,

Excel has a strange method of naming worksheets internally when it
copies them - it simply appends a 1 onto the sheet's codename, and at
some point it becomes too long. A way around it is to change the
codename of the sheet when it is copied: this example assumes that
your list of names is in a named range "MyList":

Sub CopySheet()
Dim i As Integer
For i = 1 To Range("MyList").Cells.Count
Worksheets("Sheet1").Copy After:=Worksheets(1)
ActiveSheet.Name = Range("MyList").(i).Value
ChangeSheetCodeName ActiveSheet, "Mysheet" & i
Next i
End Sub

Sub ChangeSheetCodeName(oSheet As Worksheet, sNewCodeName As String)
With oSheet.Parent.VBProject.VBComponents(oSheet.CodeName)
.Properties("_CodeName").Value = sNewCodeName
End With
End Sub

HTH,
Bernie
MS Excel MVP
 
I'm assuming that the OP is using 97 as I read an old note of Dave P's that that
bug was fixed in 2000 on.
 
Back
Top