We can transfer data from multiple workbooks into a master Excel workbook automatically using VBA.
We have, let’s say, four Excel workbooks with the names: supplier-a.xlsx, supplier-b.xlsx, supplier-c.xlsx and zmaster.xlsm in the folder ‘C:Work\Excel_Tutorial’.
Screen shots of the data containing files is given below:
Data of Supplier c
The complete macro code is given below:
Dim MyFile As String
Dim Filepath As String
Filepath = “C:\Work\Excel_Tutorial\”
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = “zmaster.xlsm” Then
Workbooks.Open (Filepath & MyFile)
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1), Cells(erow, 4))
MyFile = Dir
Note: Many thanks to Darren Elliot, a proactive website visitor, who contributed to a major correction in the above code.
Watch the Excel training video to see how the complete process of moving multiple files from one folder to another is implemented:
Range.Cells Property (Excel)
Advanced modelling in finance using Excel and VBA