How to loop through files in a directory and copy data into a master sheet in Excel?
Supposing there are multiple Excel workbooks in a folder, and you want to loop through all these Excel files and copy data from specified range of same name worksheets into a master worksheet in Excel, what can you do? This article introduces a method to achieve it in details.
If you want to copy specified data in range A1:D4 from all sheet1 of workbooks in a certain folder to a master sheet, please do as follows.
1. In the workbook you will create a master worksheet, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the code window.
VBA code: loop through files in a folder and copy data into a master sheet
Sub Merge2MultiSheets() Dim xRg As Range Dim xSelItem As Variant Dim xFileDlg As FileDialog Dim xFileName, xSheetName, xRgStr As String Dim xBook, xWorkBook As Workbook Dim xSheet As Worksheet On Error Resume Next Application.DisplayAlerts = False Application.EnableEvents = False Application.ScreenUpdating = False xSheetName = "Sheet1" xRgStr = "A1:D4" Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker) With xFileDlg If .Show = -1 Then xSelItem = .SelectedItems.Item(1) Set xWorkBook = ThisWorkbook Set xSheet = xWorkBook.Sheets("New Sheet") If xSheet Is Nothing Then xWorkBook.Sheets.Add(after:=xWorkBook.Worksheets(xWorkBook.Worksheets.Count)).Name = "New Sheet" Set xSheet = xWorkBook.Sheets("New Sheet") End If xFileName = Dir(xSelItem & "\*.xlsx", vbNormal) If xFileName = "" Then Exit Sub Do Until xFileName = "" Set xBook = Workbooks.Open(xSelItem & "\" & xFileName) Set xRg = xBook.Worksheets(xSheetName).Range(xRgStr) xRg.Copy xSheet.Range("A65536").End(xlUp).Offset(1, 0) xFileName = Dir() xBook.Close Loop End If End With Application.DisplayAlerts = True Application.EnableEvents = True Application.ScreenUpdating = True End Sub
3. Press the F5 key to run the code.
4. In the opening Browse window, please select the folder contains the files you will loop through, and then click the OK button. See screenshot:
Then a master worksheet named “New Sheet” is created at the end of current workbook. And data in range A1:D4 of all Sheet1 in selected folder is listed inside the worksheet.