help please

[jhyatt]

i have the following code that worked fine until this morning. it is now
stopping at the following line.

wksMaster.Copy after:=Worksheets(Worksheets.Count)

is there a limit to the number of sheets that you create in a workbook. the
reason i ask is while testing the code i would run it and then delete the
sheets. but now it wont get passed this line.

any help would be appreciated



Private Sub Addsheets()
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet
Dim lRow As Long

ActiveWorkbook.Unprotect Password:="53476"
Set ws = wksData
Set LastCell = ws.Cells(Rows.Count, "l").End(xlUp)
Set Rng = ws.Range("l2", LastCell)
wksTotals.Range("b5").Value = wksData.Range("L1")
Application.Goto Reference:="worksheetname"
Selection.ClearContents
Application.Goto Reference:="totalslist"
Selection.ClearContents
For Each cell In Rng
If Not cell.Value = "" Then
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(cell.Value)
On Error GoTo 0
If ws Is Nothing Then
wksMaster.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Visible = True
ActiveSheet.Name = cell.Value & cell.Offset(0, 1).Value
With Sheets("data")
lRow = .Cells(.Rows.Count, "V").End(xlUp).Row
.Cells(lRow + 1, "V").Value = ActiveSheet.Name
End With
With Sheets("data")
lRow = .Cells(.Rows.Count, "w").End(xlUp).Row
.Cells(lRow + 1, "w").Value = "='" &
ActiveSheet.Name & "'!k30"
End With
With Sheets("data")
lRow = .Cells(.Rows.Count, "x").End(xlUp).Row
.Cells(lRow + 1, "x").Value = "='" &
ActiveSheet.Name & "'!L30"
End With
With Sheets("data")
lRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
.Cells(lRow + 1, "Y").Value = "='" &
ActiveSheet.Name & "'!m30"
End With
With Sheets("data")
lRow = .Cells(.Rows.Count, "z").End(xlUp).Row
.Cells(lRow + 1, "z").Value = "='" &
ActiveSheet.Name & "'!R30"
End With
With Sheets("data")
lRow = .Cells(.Rows.Count, "aa").End(xlUp).Row
.Cells(lRow + 1, "aa").Value = "='" &
ActiveSheet.Name & "'!S30"
End With
With Sheets("data")
lRow = .Cells(.Rows.Count, "ab").End(xlUp).Row
.Cells(lRow + 1, "ab").Value = "='" &
ActiveSheet.Name & "'!T30"
End With
With Sheets("data")
lRow = .Cells(.Rows.Count, "ac").End(xlUp).Row
.Cells(lRow + 1, "ac").Value = "='" &
ActiveSheet.Name & "'!V30"
End With
Range("I3").Value = cell.Offset(0, 2).Value
End If
End If
Next
wksTotals.Activate
wksTotals.Name = Range("b5").Value & "Totals"
ActiveWorkbook.Protect Password:="53476"
End Sub

 

[Joel]

You can only add a maximum of 40 worksheets at one time without closing the
workbook and reopening the workbook. There are other postings about this
issue if you do a search.

 

[Jim Cone]

From a post by RagDyer yesterday (10/10/2007)...
'---
"Just as a topic of conversation, since I doubt anyone would go to these
lengths:
Max sheets is 5,447
As tested by Dana in XL07,
And verified by Harlan in XL03.
Attempting to insert the 5,448th caused both versions to crash!--
Regards,
RD"
'---
From Jim Cone today...
There may be some help here...
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684
"Copying worksheet programmatically causes run-time error...
'---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"jhyatt"
wrote in message
i have the following code that worked fine until this morning. it is now
stopping at the following line.

wksMaster.Copy after:=Worksheets(Worksheets.Count)

is there a limit to the number of sheets that you create in a workbook. the
reason i ask is while testing the code i would run it and then delete the
sheets. but now it wont get passed this line.
any help would be appreciated

-snip-

 

[jhyatt]

seems to work fine now i tried searching but i must have used the wrong
keywords because i couldnt find anything.

thank you for your help

 

[jhyatt]

thank you very helpful info