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.
Recommended Productivity Tools
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
1. In the code, “A1:D4” and “Sheet1” mean that data in range A1:D4 of all Sheet1 will be copied into the master sheet. And “New Sheet” is the name of the new created master sheet.
2. The Excel files in the specific folder should not open.
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.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...