Trying to compile a bunch of worksheets from different .xls files into one spreadsheet

[ethaneandethaneproducts]

Forgive my cluelessness, I'm new to power query and the like. I have monthly spreadsheets going back quite a few years, and there's one tab/worksheet from each that I want compiled into my new spreadsheet (let's call the worksheet "slime", which I'm trying to move into my new spreadsheet, "historical slime"). It'd take me all day to manually open, move or copy, copy to the new file I'm making - is there a good way to go about automating this? Only nuances involved: the files are all excel 97-03 worksheets (.xls), I'm doing this in excel 2016, and the worksheet name "slime" is identical in each and every file. Wouldn't be very time consuming for me to go through and manually rename them "MonthYear" once they're in the consolidated spreadsheet though.

Thanks in advance

 

[Logit]

Place your Master Workbook in the same Folder as all the .xls files that will be copied.

Paste the following into a routine module :

Sub Maybe_Like_So()
Dim wb As String
    Application.ScreenUpdating = False
    wb = Dir(ThisWorkbook.Path & "\*")
    
    Do Until wb = ""
    
        If wb <> ThisWorkbook.Name Then
        
            Workbooks.Open ThisWorkbook.Path & "\" & wb
            
                With Workbooks(wb).Sheets("Slime")
                    .UsedRange.Offset(1).Copy ThisWorkbook.Sheets("Historical Slime").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                End With
            
            Application.CutCopyMode = False
            Workbooks(wb).Close False
            
        End If
        
        wb = Dir
    
    Loop
    Application.ScreenUpdating = True
    
End Sub

The above macro should copy only the tabs named "Slime" into the Master Workbook / Sheet "Historical Slime" ... one below the other.

Your Master Workbook can be named anything you want. The sheet to be pasted to should be named "Historical Slime".

 

[ethaneandethaneproducts]

Place your Master Workbook in the same Folder as all the .xls files that will be copied.

Paste the following into a routine module :

Sub Maybe_Like_So()
Dim wb As String
    Application.ScreenUpdating = False
    wb = Dir(ThisWorkbook.Path & "\*")
   
    Do Until wb = ""
   
        If wb <> ThisWorkbook.Name Then
       
            Workbooks.Open ThisWorkbook.Path & "\" & wb
           
                With Workbooks(wb).Sheets("Slime")
                    .UsedRange.Offset(1).Copy ThisWorkbook.Sheets("Historical Slime").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                End With
           
            Application.CutCopyMode = False
            Workbooks(wb).Close False
           
        End If
       
        wb = Dir
   
    Loop
    Application.ScreenUpdating = True
   
End Sub

The above macro should copy only the tabs named "Slime" into the Master Workbook / Sheet "Historical Slime" ... one below the other.

Your Master Workbook can be named anything you want. The sheet to be pasted to should be named "Historical Slime".

Thank you, seems to be working for me. Gonna have to do a lot of cleaning up but this is a lot better than opening each individual file.

 

[ethaneandethaneproducts]

Thank you, seems to be working for me. Gonna have to do a lot of cleaning up but this is a lot better than opening each individual file.

And it has broken excel to an impressive degree. I now have a blank workbook in which I can't select any cells, and I can scroll, but the row/column numbers don't change. Maybe doing 30 months worth at once was a bad idea?

 

[ethaneandethaneproducts]

And it has broken excel to an impressive degree. I now have a blank workbook in which I can't select any cells, and I can scroll, but the row/column numbers don't change. Maybe doing 30 months worth at once was a bad idea?

Saving and reopening revealed the data. Solved! Thanks to Logit.

 

[Logit]

Glad you have an answer !

I was going to suggest what you found on your own. Periodically saving the Master Workbook.
I am curious if implementing a 'pause' between each 'COPY / PASTE ' in conjunction with the
'saving' would also give the macro an opportunity to 'catch up' with the processing.

Cheers

 

[ethaneandethaneproducts]

Glad you have an answer !

I was going to suggest what you found on your own. Periodically saving the Master Workbook.
I am curious if implementing a 'pause' between each 'COPY / PASTE ' in conjunction with the
'saving' would also give the macro an opportunity to 'catch up' with the processing.

Cheers

I think half the problem was that each of these monthly workbooks have 10+ sheets and contain links to another 10 workbooks.