Macro pulls multiple workbooks into one workbook - but need to pull in over 300 workbooks

[rachel06]

Hi!

I have a macro that pulls in sheets from a specified folder into one workbook. There's a slight problem with this - this is used for a coalition of clients with over 300 members! The max number of worksheets in a workbook is around 255. Is there a way to tell the macro to open a new workbook and start putting the sheets there if need be or another way around this?

We need them all in the workbook separate because we also use the macro to save and rename them individually.


TIA!

 

[igold]

From what I am reading from a Google search, there is no theoretical maximum for worksheets. The 255 limit refers to how many sheets can be created when opening a new workbook. If the workbook is already created then the limit is dictated by available memory.

 

[jolivanes]

A folder has no sheets. I assume you mean sheets from a workbook in a specific folder.
Is this from each workbook in a folder? Are there any other files/workbooks in that folder where no sheets need to be extracted? Maybe non excel files or some workbooks that need to be excluded.
Which sheets need to be copied or extracted? The first sheet or sheets with a specific name? As you mention "pulling sheets" that to me means cutting the sheet and pasting it in a different sheet.

If your master workbook, the workbook that will receive all the copied sheets, has been saved in the same folder that has all the workbooks that you need a sheet, in this code the first sheet, copied from, this will do that.
Code should be copied into a regular module in the master workbook.

Sub Copy_Sheet1_Into_Master()
Dim mydir As String, myfile As String, mybook As Workbook, j As Long

    j = 0
    mydir = ThisWorkbook.path & "\"
    myfile = Dir(mydir & "*.xl*")

    Application.ScreenUpdating = False

    Do While myfile <> ""
        If myfile <> ThisWorkbook.Name Then
        Set mybook = Workbooks.Open(mydir & myfile)
            j = j + 1
            mybook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                ThisWorkbook.Sheets(Sheets.Count).Name = "New Sheet " & j
        mybook.Close False
        End If
        myfile = Dir()
    Loop

    Application.ScreenUpdating = True

End Sub

 

[jolivanes]

Did anything work?

 

[rachel06]

Did anything work?

Sorry I got tied up in other work after posting this. The note about it being dictated by memory cleared up my issue, but I'll certainly keep this thread handy in case it doesn't work for others. I just got a new computer with 4x the memory and I'm not sure others have that!

 

[igold]

I am glad my note helped you, and thank you for the feedback.