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.
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!