wilg
12-17-2010, 08:25 PM
Looks like there is a known bug in copying multiple worksheets at a time.
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684
I'm trying for a workaround that will count to 10 sheets the close and reopen. Right now this will bug out after 42 sheets. If I put in from ("A2:A35") the code works.
Does anyone have a solution to help me complete the code below to open and close after each set of 10 worksheets?
Thanks in advance.
Sub ADDSHEETS()
Dim tabname As Range
For Each tabname In Sheets("2 STATUS").Range("A2:A300")
If tabname.Value <> "" Then
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(tabname.Value)
On Error GoTo 0
If sh Is Nothing Then
Worksheets("4 COPY").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = tabname.Value
End If
End If
Next tabname
ThisWorkbook.Save
On Error Resume Next
Application.OnTime Now + TimeValue("00:00:3"), "OpenMe1"
On Error GoTo 0
ThisWorkbook.Close False
End Sub
Sub OpenMe1()
Call SortWorksheets
MsgBox "Finished updating."
End Sub
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684
I'm trying for a workaround that will count to 10 sheets the close and reopen. Right now this will bug out after 42 sheets. If I put in from ("A2:A35") the code works.
Does anyone have a solution to help me complete the code below to open and close after each set of 10 worksheets?
Thanks in advance.
Sub ADDSHEETS()
Dim tabname As Range
For Each tabname In Sheets("2 STATUS").Range("A2:A300")
If tabname.Value <> "" Then
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(tabname.Value)
On Error GoTo 0
If sh Is Nothing Then
Worksheets("4 COPY").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = tabname.Value
End If
End If
Next tabname
ThisWorkbook.Save
On Error Resume Next
Application.OnTime Now + TimeValue("00:00:3"), "OpenMe1"
On Error GoTo 0
ThisWorkbook.Close False
End Sub
Sub OpenMe1()
Call SortWorksheets
MsgBox "Finished updating."
End Sub