Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help please
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. "jhyatt" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help please
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...b;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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help please
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 "Joel" wrote: 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. "jhyatt" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help please
thank you very helpful info
"Jim Cone" wrote: 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...b;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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|