Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Can't Add a New Worksheet

    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?
    Thanks.
    Jerry

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    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.
    Code:
    Sub AddNewSheets()
    Dim index As Integer
    Dim index2 As Integer
    For index2 = 1 To 50
        Application.StatusBar = " processing " & index2
        For index = 1 To 250
            Worksheets.Add
            Worksheets("Sheet1").Range("A1") = _
                Worksheets("Sheet1").Range("A1") + 1
        Next index
        RemoveSheets
    Next index2
    Application.StatusBar = "Ready"
    End Sub
    
    Sub RemoveSheets()
    Dim aSheet As Object
    
    For Each aSheet In ActiveWorkbook.Sheets
        If aSheet.Name <> "Sheet1" Then
            Application.DisplayAlerts = False
            Worksheets(aSheet.Name).Delete
            Application.DisplayAlerts = True
        End If
    Next
    End Sub

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    I believe that the number is limited by the RAM limitations of the computer itself.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •