Is there a limit to the number of workbooks you can 'Move' into a new workbook

[berlinhammer]

Hello,

I posted a question on a different area of the macro I'm working on, but I think this is a different issue altogether so I hope it warrants another thread.

My macro loops through my worksheets in my workbook (wbO), it copies them, if they pass certain criteria they are moved to a new workbook I create at the beginning of the macro (wbN), if they fail they are deleted.

My macro keeps tripping after about 7 sheets, I do not think my sheets are protected at all.

ERROR (paraphrased)
Run time 1004
Method 'Move' of object 'Worksheet' failed

The strange thing I have found is that as the macro hangs in debug mode, I seem to be able to move the sheet manually to a another new book, and then back from the new book to wbO but if I try to move anything to wbN it simply does not do anything.

The only conclusion I can draw from this is that wbN does not want any more sheets, has it hit some kind of limit? I seem to be able to copy or move sheets or whatever but just not into wbN.

I'm puzzled by this, it would be interesting to know if anyone has experience anything similar or knows how I can fix it?

Thanks

Jon

 

[Domenic]

According to Excel's help file under 'Excel Specifications and Limits', the number of sheets allowed in a workbook is limited only by available memory. Try posting your code and someone may be able to help.

 

[berlinhammer]

Thanks for replying Domenic. Here is the code.

If it's a memory problem it seems an odd way for it to be manifesting itself!

Very interested to see exactly what is causing this...

Sub ExportValueSheets()

'''     Ensure "Convert to Values" and "Export"
'''     columns are correctly populated!!

    Dim wbO As Workbook, wbN As Workbook
    Dim wsO As Worksheet, wsN As Worksheet
    Dim rngShNames As Range, rng As Range, cell As Range
    Dim iCells As Long, iBlanks As Long, iNonBlanks As Long, l As Long
    Dim strShName As String
    
    Const firstcelladdress = "D26"
    
    Set wbO = ThisWorkbook
    Set wbN = Workbooks.Add
    wbO.Activate
    
    Set rngShNames = wbO.Sheets("KEY").Range("rExportAsValues").Columns(1)
    Set rngShNames = Intersect(rngShNames, rngShNames.Offset(1, 0))
    
    iCells = rngShNames.Cells.Count
    iBlanks = rngShNames.SpecialCells(xlCellTypeBlanks).Cells.Count
    iNonBlanks = iCells - iBlanks
    
    For l = 1 To iNonBlanks
        
        wbO.Sheets(1).Activate
        
        strShName = rngShNames.Cells(l).Value
    
        Set wsO = wbO.Sheets(strShName)
        wsO.Copy After:=wbO.Sheets(1)
        Set wsN = wbO.Sheets(2)
        wsN.Name = "temp_sheet"
        
        wbO.Sheets(1).Activate

        Select Case rngShNames.Offset(0, 1).Cells(l).Value
            Case "Y"
                Set rng = wsN.Range(firstcelladdress).CurrentRegion
                rng.Value = rng.Value
                Set rng = Nothing
            Case "N"
        End Select
    
        wbO.Sheets(1).Activate
    
        Select Case rngShNames.Offset(0, 2).Cells(l).Value
            Case "Y"
                wsN.Move After:=wbN.Sheets(wbN.Sheets.Count)
                Set wsN = wbN.Sheets(wbN.Sheets.Count)
                wsN.Name = strShName
            Case "N"
                wsN.Delete
        End Select
    
    Set wsO = Nothing
    Set wsN = Nothing
    
    Next

End Sub

 

[berlinhammer]

Just to explain. The two Case statements go down a list of Yor N values to decide what to do.

This bit decides whether to convert sheet's formulas to values

        Select Case rngShNames.Offset(0, 1).Cells(l).Value
            Case "Y"
                Set rng = wsN.Range(firstcelladdress).CurrentRegion
                rng.Value = rng.Value
                Set rng = Nothing
            Case "N"
        End Select
    
        wbO.Sheets(1).Activate

This bit decide whether to move the sheet to the new workbook

        Select Case rngShNames.Offset(0, 2).Cells(l).Value
            Case "Y"
                wsN.Move After:=wbN.Sheets(wbN.Sheets.Count)
                Set wsN = wbN.Sheets(wbN.Sheets.Count)
                wsN.Name = strShName
            Case "N"
                Application.DisplayAlerts = False
                wsN.Delete
                Application.DisplayAlerts = True
        End Select

I think a new approach may not be a bad idea, ie just deleting sheets and saving the workbook under a different name, thus keeping the original.

I'd still like to understand this error better though.