I am developing Excel reports in a workbook which I chose to delete 31 worksheets when builing new monthly reports with VBA after copying the worksheets from a template, then I delete them and rebuild new ones for the next month.
The workbook has about a dozen tables for vlookups. At this point, it appears that Excel will not allow me to add worksheets. Is there a limit on how many worksheets you can add, even though they have been deleted?
Is there a way to reset the workbook to so that this limitation will not happen again?
I have been running macros to add and delete worksheets during the development phase. At a certian point, the macro could not add another worksheet and I could not add one manually. When I closed the workbook and opened it again, everything worked normally. Resolved.
As a test, I ran the code below 4 times, in a new workbook, and created and deleted 50,000 worksheets in the same workbook. That tells me that there is no limit (that I will ever encounter) to the number of sheets created and deleted in a workbook.
Dim index As Integer
Dim index2 As Integer
For index2 = 1 To 50
Application.StatusBar = " processing " & index2
For index = 1 To 250
Worksheets("Sheet1").Range("A1") = _
Worksheets("Sheet1").Range("A1") + 1
Application.StatusBar = "Ready"
Dim aSheet As Object
For Each aSheet In ActiveWorkbook.Sheets
If aSheet.Name <> "Sheet1" Then
Application.DisplayAlerts = False
Application.DisplayAlerts = True