Merge Workbooks (30+)


I need your assiatnce because I would like to export content/rows of data from multiple workbooks (30+) to a master workbook.

The code below is working properly for me, because I can see that it opens all workbooks inside the folder “merge2”, however only the data from the last three workbooks is captured when the transfer is completed.

Could you please help me adapt the code below to be able to extract and retain any number of workbooks I save under folder “merge2”?


Thank you in advance for your help.


VBA code:


Sub copyDataFromMupltipleWorkbooksIntoMaster()

Dim FolderPath As String, FilePath As String, FileName As String

FolderPath = “F:merge2”

FilePath = FolderPath & “*.xls*”

FileName = Dir(FilePath)

Dim lastrow As Long, lastcolumn As Long

Do While FileName <> “”
Workbooks.Open (FolderPath & FileName)

lastrow = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Range(Cells(7, 2), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“sheet1”).Range(Cells(erow, 2), Cells(erow, 9))

FileName = Dir

Application.DisplayAlerts = True

End Sub

By: IA

Leave a Reply

Your email address will not be published. Required fields are marked *