Transfer Data Multiple Workbooks Master Workbook Automatically

Home More Excel (VBA) Training Videos








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:

supplier-a-data

supplier-b-data

supplier-c-data

zmaster-data
The complete macro code is given below:

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "C:\Work\Excel_Tutorial\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
    If MyFile = "zmaster.xlsm" Then
    Exit Sub
    End If
   
    Workbooks.Open (Filepath & MyFile)
    Range("A2:D2").Copy
    ActiveWorkbook.Close
   
    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
Loop
End Sub


Watch the Excel training video to see how the complete process of moving multiple files from one folder to another is implemented:


Watch this video on YouTube
Reference
Home More Excel (VBA) Training Videos