VBA code to loop through and compile specific sheets from multiple workbooks

Question

I have a folder with ~20 workbooks that all have different file names and I need to pull off a specific worksheet from each workbook into 1 main workbook. I'm not familiar in VBA but I was hoping someone can turn my pseudocode into a working VBA script for Excel.

create wrkbk_names as array
store workbook names in "folder" to wrkbk_names
for each book in wrkbk_names
    for each worksheet in book
         if worksheet.name = "targetSheet"
              copy worksheet("targetSheet") to Mainbook.xlxs

The workbooks are extremely large with 50+ sheets/book so if I could open them without activation that would speed up the process quite a lot. The specific worksheet I'm trying to extract has the same name across all workbooks although I'm not too sure about the index.

Bonus points would be if the script can copy the cells from the worksheet to the next empty row of Mainbook so that all of the targetSheet data from 20 workbooks are compiled to 1 worksheet of Mainbook.

If all the sheets in all the workbook have the same structure and saved at one place in a particular folder, try using Power Query. Search "How to combine files in Power Query" on YouTube, you will get enough information about how you can achieve it easily. — Subodh Tiwari sktneer

Zev Spitz Zev Spitz · Accepted Answer · 2017-06-27T06:22:40

If you have a particular preference for doing this with VBA, consider using the Scripting.FileSystemObject (add a reference to Microsoft Scripting Runtime via Tools -> References...):

'Assuming you are running this within Excel
'otherwise you'll have to create an Excel instance

'This also assumes that the target workbook is the currently active workbook
'in the Excel instance

Dim mainBook As Workbook
Set mainBook = ActiveWorkbook

Dim fso As New Scripting.FileSystemObject
Dim fle As Scripting.File
Dim book As Workbook
For Each fle In fso.GetFolder("C:\folder").Files

    'Should probably check here that the file is actually an Excel file

    Set book = Workbooks.Open(fle.Path)
    Dim wks As Worksheet
    For Each wks In book.Worksheets
        If wks.Name = "targetSheet" Then
            wks.Copy mainBook.Worksheets(1) 'copies to the start of the main workbook
        End If
    Next
    book.Close
Next

VBA code to loop through and compile specific sheets from multiple workbooks

1 Answers