In this article, we’ll illustrate how to extract data from multiple Excel files that are in a specific folder into a single workbook using Macro. The extracted data can be collected into single or multiple sheets of that workbook.
Extract Data from Multiple Excel Files Using a Macro: 4 Examples
Let’s say we have multiple Excel files in a folder in our computer storage. In this case, we’ve created a folder named saledata in the Local Disk D to hold the Excel files.
The Excel files contain sale data for 5 consecutive days for a fruit shop. For example, in the file named Day1.xlsx, we have the sale details of the products of the date 1/1/2020.
We want to extract these sale data from the Excel files of the folder and collect them into one single file that must be in another location other than this folder.
Things We Should Learn First
In our macro, we’ll use the FileSystemObject object to access our local computer’s file system to get the files inside the folder.
Then, we need to put the folder path correctly to get access to the Excel files in it. To get the folder location, we need to-
- Open the folder
- Then click on the address bar in the windows file explorer
- Copy the highlighted address with Ctrl + C.
We’ll use the For Each loop to loop through all the Excel files inside the folder and the For Next Loop to extract data from the source files and paste them to the new file.
Write Code in Visual Basic Editor
To extract data from multiple Excel files to a new workbook, we’ll use several VBA functions and properties in our code. The following section describes how to open and write code in the visual basic editor.
- Right-click on the sheet name.
- Choose the View Code option.
- Now put your macro in the visual basic editor.
- Press F5 to run the code.
1. Run a Macro to Extract Data from Multiple Excel Files to a Single Workbook
At first, we created a new Excel workbook to collect the extracted data from different files. As we mentioned earlier, the new workbook must be outside the folder that has the source Excel files.
1.1 Collect the Extracted Data into Different Worksheets
In this illustration, we’ll extract data from multiple Excel files and then collect them in different worksheets. Copy and paste the macro into the visual code editor and press F5 to run it.
Sub ExtractDataToDifferentSheets()
On Error GoTo HandleError
Application.ScreenUpdating = False
Dim objectFlieSys As Object
Dim objectGetFolder As Object
Dim file As Object
Set objectFlieSys = CreateObject("Scripting.FileSystemObject")
Set objectGetFolder = objectFlieSys.GetFolder("D:\saledata") ' The folder location of the source files.
Dim counter As Integer
counter = 1
For Each file In objectGetFolder.Files
Dim sourceFiles As Workbook
Set sourceFiles = Workbooks.Open(file.Path, True, True)
Dim rowsNumber As Integer
rowsNumber = sourceFiles.Worksheets("sheet1").UsedRange.rows.Count
Dim colsNumber As Integer
colsNumber = sourceFiles.Worksheets("sheet1").UsedRange.Columns.Count
Dim rows, cols As Integer
For rows = 1 To rowsNumber
For cols = 1 To colsNumber
Application.Workbooks(1).ActiveSheet.Cells(rows, cols) = _
sourceFiles.Worksheets("Sheet1").Cells(rows, cols)
Next cols
Next rows
rows = 0
Dim worksheetName As String
worksheetName = Replace(sourceFiles.Name, ".xlsx", "")
sourceFiles.Close False
Set sourceFiles = Nothing
With ActiveWorkbook
.ActiveSheet.Name = worksheetName
counter = counter + 1
If counter > .Worksheets.Count Then
.Sheets.Add After:=.Worksheets(.Worksheets.Count)
End If
.Worksheets(counter).Activate
End With
Next
HandleError:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
We’ve successfully extracted and collected the data into 5 different sheets in the same workbook. We configured the code to create new sheets in the workbook according to the number of Excel files in the folder. And then the newly created sheets are named according to the source file names (Day1, Day2….) with the extracted data in it.
1.2 Extract Data into a Single Worksheet
In this illustration, we’ll extract and then collect data from multiple files into one single worksheet of the new workbook. To do that, copy, paste, and then run the following macro in the visual basic editor.
Sub ExtractDataToSingleFile()
On Error GoTo HandleError
Application.ScreenUpdating = False
Dim objectFileSys As Object
Dim objectGetFolder As Object
Dim file As Object
Set objectFileSys = CreateObject("Scripting.FileSystemObject")
Set objectGetFolder = objectFileSys.GetFolder("D:\saledata") ' The folder location for the source files.
Dim counter As Integer
counter = 0
For Each file In objectGetFolder.Files
Dim sourceFiles As Workbook
Set sourceFiles = Workbooks.Open(file.Path, True, True)
Dim rowsNumber As Integer
rowsNumber = sourceFiles.Worksheets("sheet1").UsedRange.rows.Count
Dim colsNumber As Integer
colsNumber = sourceFiles.Worksheets("sheet1").UsedRange.Columns.Count
Dim rows, cols As Integer
For rows = 1 To rowsNumber
For cols = 1 To colsNumber
Cells(rows + counter, cols) = sourceFiles.Worksheets("Sheet1").Cells(rows, cols)
Next cols
Next rows
counter = counter + rows
rows = 0
sourceFiles.Close False
Set sourceFiles = Nothing
Next
HandleError:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Here is the output in the following screenshot.
Read More: How to Pull Data from Multiple Worksheets in Excel
2. Extract and Then Merge Data from Multiple Excel Files to a Single File Using a Marco
In this illustration, we’ll extract data from different files and merge them into one file. As every dataset in the files in the folder has a header, we’ll keep the header only for the first file. Let’s copy and paste the following macro into the visual basic editor.
Sub ExtractMergeDataFromMultipleFiles()
Dim i As Long
Dim currentRow As Long
Dim row As Long
Dim wBook As workBook
For i = 1 To 5 Step 1
Set wBook = Workbooks.Open("D:\saledata" & "\Day" & i & ".xlsx")
With wBook.Sheets("Sheet1")
If i = 1 Then
row = 1
Else
row = 2
End If
Do Until .Range("A" & row).Value = vbNullString
currentRow = currentRow + 1
For n = 0 To 4 Step 1
Me.Range("A" & currentRow).Offset(columnoffset:=n).Value = .Range("A" & row).Offset(columnoffset:=n).Value
Next n
row = row + 1
Loop
End With
wBook.Close True
Next i
Set wBook = Nothing
End Sub
The following screenshot shows the merged dataset.
Code Explanation:
In the code, there are two loops. The first For Next loop was set for 5 iterations as we have 5 files in the folder.
For i = 1 To 5 Step 1
And we set the second loop to iterate 5 times (n=0 to 4) as we have 5 columns in our dataset.
For n = 0 To 4 Step 1
3. Set Range to Extract Data from Multiple Files to a Single Workbook Using a Macro in Excel
The following macro facilitates us to choose the range of data that we want to extract from the dataset into a new workbook. In this example, we want to extract only the first two rows of data from each of the Excel files.
To accomplish this, we need to set the range as A1:E3 in the following macro. Now copy and paste in the visual basic editor.
Sub ExtractDataMuilpleFiles()
Dim location As String, files As String
Dim eFiles() As String
Dim rowCount As Long, fileNum As Long
Dim wBook As Workbook, masterSheet As Worksheet
Dim srcRng As Range, dstRng As Range
Dim rowNum As Long, calType As Long
' Put the folder location
location = "D:\saledata"
' This will put a Slash if necessary
If Right(location, 1) <> "\" Then
location = location & "\"
End If
files = Dir(location & "*.xl*")
If files = "" Then
MsgBox "Not Found"
Exit Sub
End If
fileNum = 0
Do While files <> ""
fileNum = fileNum + 1
ReDim Preserve eFiles(1 To fileNum)
eFiles(fileNum) = files
files = Dir()
Loop
With Application
calType = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
Set masterSheet = ActiveWorkbook.ActiveSheet
rowNum = 1
If fileNum > 0 Then
For fileNum = LBound(eFiles) To UBound(eFiles)
Set wBook = Nothing
On Error Resume Next
Set wBook = Workbooks.Open(location & eFiles(fileNum))
On Error GoTo 0
If Not wBook Is Nothing Then
On Error Resume Next
' Put the Range to Extract Data from
With wBook.Worksheets(1)
Set srcRng = .Range("A1:E3")
End With
If Err.Number > 0 Then
Err.Clear
Set srcRng = Nothing
Else
If srcRng.Columns.Count >= masterSheet.Columns.Count Then
Set srcRng = Nothing
End If
End If
On Error GoTo 0
If Not srcRng Is Nothing Then
rowCount = srcRng.rows.Count
If rowNum + rowCount >= masterSheet.rows.Count Then
MsgBox "Not enough rows in target worksheet."
masterSheet.Columns.AutoFit
wBook.Close savechanges:=False
GoTo ExitTheSub
Else
Set dstRng = masterSheet.Range("A" & rowNum)
With srcRng
Set dstRng = dstRng. _
Resize(.rows.Count, .Columns.Count)
End With
dstRng.Value = srcRng.Value
rowNum = rowNum + rowCount
End If
End If
wBook.Close savechanges:=False
End If
Next fileNum
masterSheet.Columns.AutoFit
End If
ExitTheSub:
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = calType
End With
End Sub
As the output, we’ve extracted only two rows of data from the dataset of each source files to the newly created worksheet.
Set your own data range in the following line of the macro.
Set srcRng = .Range("A1:E3")
Read More: How to Pull Data from Multiple Worksheets in Excel VBA
4. Extract Data from Selected Files into One Workbook Using Macro in Excel
We’ve configured the previous macro so that we can choose Excel files from the file explorer. This way we can specify the source files to extract data from. Let’s say we want to select and extract data only from 1st 3 files in the folder. Let’s follow the steps below to accomplish this.
- Copy and paste the following macro into the visual basic editor.
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
SetCurrentDirectoryA szPath
End Sub
Sub ExtractDataFromSelectedFiles()
Dim eLocation As String
Dim rowCount As Long, filesNum As Long
Dim wBook As Workbook, masterSheet As Worksheet
Dim srcRng As Range, dstRng As Range
Dim rowNum As Long, calType As Long
Dim saveLocation As String
Dim fileName As Variant
With Application
calType = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
saveLocation = CurDir
' Change this to the path\folder location of the files.
ChDirNet "D:\saledata"
fileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
MultiSelect:=True)
If IsArray(fileName) Then
Set masterSheet = ActiveWorkbook.ActiveSheet
rowNum = 1
For filesNum = LBound(fileName) To UBound(fileName)
Set wBook = Nothing
On Error Resume Next
Set wBook = Workbooks.Open(fileName(filesNum))
On Error GoTo 0
If Not wBook Is Nothing Then
On Error Resume Next
With wBook.Worksheets(1)
Set srcRng = .Range("A1:E4")
End With
If Err.Number > 0 Then
Err.Clear
Set srcRng = Nothing
Else
If srcRng.Columns.Count >= masterSheet.Columns.Count Then
Set srcRng = Nothing
End If
End If
On Error GoTo 0
If Not srcRng Is Nothing Then
rowCount = srcRng.rows.Count
If rowNum + rowCount >= masterSheet.rows.Count Then
MsgBox "not enough rows in target worksheet."
masterSheet.Columns.AutoFit
wBook.Close savechanges:=False
GoTo ExitTheSub
Else
Set dstRng = masterSheet.Range("A" & rowNum)
With srcRng
Set dstRng = dstRng. _
Resize(.rows.Count, .Columns.Count)
End With
dstRng.Value = srcRng.Value
rowNum = rowNum + rowCount
End If
End If
wBook.Close savechanges:=False
End If
Next filesNum
masterSheet.Columns.AutoFit
End If
ExitTheSub:
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = calType
End With
ChDirNet saveLocation
End Sub
- Run the macro by pressing F5.
- Go to the folder location in the file explorer.
- Select the desired files.
- Click the Open
- The macro successfully extracted data to the new worksheet.
Here in the code, we set the range as A1:E4 i.e., the whole dataset from the source files to extract and collect as output.
Things to Remember
All the macros we used in the above examples search data in the worksheet named “Sheet1” i.e., sheet number 1 from the source workbook, and then extract them to the new workbook.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to extract data from multiple Excel files into one workbook using macro with different examples. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Hello,
Thank you for what you are doing here. I would like to use “1. Run a Macro to Extract Data from Multiple Excel Files to a Single Workbook” however it only grabs the first file in the folder and doesnt change the worksheet name. Any idea why this would happen? the only thing I changed from your code is the file path
Thank you!
Hi Jeff V,



Thanks for reaching us. You have informed us here that the aforementioned code is not giving your expected out. But in my case, I am getting the correct outputs by extracting data from different workbooks into one. I think yours will also work fine if you notice the following matters.
• Firstly, copy the exact path name where your desired files are saved.
• Put down the correct sheet name of your saved workbooks in the following indicated areas.
After modifying all of these factors, run your final code.
Sub ExtractDataToDifferentSheets()
On Error GoTo HandleError
Application.ScreenUpdating = False
Dim objectFlieSys As Object
Dim objectGetFolder As Object
Dim file As Object
Set objectFlieSys = CreateObject(“Scripting.FileSystemObject”)
Set objectGetFolder = objectFlieSys.GetFolder(“D:\Monthly Sales”)
Dim counter As Integer
counter = 1
For Each file In objectGetFolder.Files
Dim sourceFiles As Workbook
Set sourceFiles = Workbooks.Open(file.Path, True, True)
Dim rowsNumber As Integer
rowsNumber = sourceFiles.Worksheets(“Sheet1”).UsedRange.rows.Count
Dim colsNumber As Integer
colsNumber = sourceFiles.Worksheets(“Sheet1”).UsedRange.Columns.Count
Dim rows, cols As Integer
For rows = 1 To rowsNumber
For cols = 1 To colsNumber
Application.Workbooks(1).ActiveSheet.Cells(rows, cols) = _
sourceFiles.Worksheets(“Sheet1”).Cells(rows, cols)
Next cols
Next rows
rows = 0
Dim worksheetName As String
worksheetName = Replace(sourceFiles.Name, “.xlsx”, “”)
sourceFiles.Close False
Set sourceFiles = Nothing
With ActiveWorkbook
.ActiveSheet.Name = worksheetName
counter = counter + 1
If counter > .Worksheets.Count Then
.Sheets.Add After:=.Worksheets(.Worksheets.Count)
End If
.Worksheets(counter).Activate
End With
Next
HandleError:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Finally, you will get the following sheets in a single workbook.

Hi what modifications should be fade if i want to combine files with more than one sheets.
Hello, HARVEINDRAN!
Please check the following articles. Hopefully, this will solve your problem!
https://www.exceldemy.com/excel-combine-data-from-multiple-sheets/
https://www.exceldemy.com/combine-multiple-excel-files-into-one-workbook-separate-sheets/
Hey thank u so much for this guide and making it so easy to understand. There’s any chance that u can explain how to extract info from selected cells in a worksheet?
Hello, DANIEL.
Thanks for reading our articles.
Look at the below link. Hopefully, you will get your solution.
https://www.exceldemy.com/extract-data-from-one-sheet-to-another-in-excel-using-vba/
For example, you can use the following code:
Sub Extract_Data()
Selection.Copy
Sheets("Dataset2").Activate
Range("F4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Enter your sheet name instead of Dataset2 in the 3rd line. Change the cell range in the 4th line. Hope you will get desired output. If your problem is yet solved, then let us know.
Regards.
-Alok Paul
Author at ExcelDemy
I HAVE SOME WORKBOOK IN DIFFERENT FOLDERS, SALE DATA > YEAR(2023,2022,2021) > MONTH(JAN,FEB) AND THEN EXCEL WORK BOOK . I WANT TO COLLECT SALE DATA FROM ALL 3 YEARS.
IS IT POSSIBLE?
Hi BICKY,

Thank you for your comment. According to your comment, I understand that you have a folder named SALE DATA and in that folder, you have another 3 folders named 2021, 2022 and 2023. Each sales year folder has two folders (JAN, and FEB) and then an Excel workbook. You want to collect sales data from all 3 years in a single workbook.
To solve this issue follow the below steps:
● Insert a new module and copy and paste the following code.
● Set the base folder path and the target sheet where you want to collect the data according to your PC.

● Run the macro by pressing F5.
You can see that the macro successfully extracted data to the new worksheet. You can download the Excel file below.
Answer.xlsm
Hopefully, you will be able to solve your problem now. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.
Regards
Nujat Tasnim
Exceldemy.