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.
Loop through files in a directory and copy data into a master sheet with VBA code
Loop through files in a directory and copy data into a master sheet with VBA code
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 SubNote:
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.
Related articles:
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!
