How to transfer multiple rows of data from multiple workbooks into master workbook with VBA


How can we transfer multiple rows of data from multiple workbooks into a master workbook using VBA. Earlier we had published a post covering how to transfer a row of data from 3 files called supplier-a.xlsx, supplier-b.xlsx and supplier-c.xlsx into a master file called zmaster.xlsm. In this post we had exited the subroutine or macro if the filename was zmaster.xlsm. Now many of our viewers wanted to know:

  • How to transfer multiple rows of data from the suppliers files into the master file
  • What if the master file name were not zmaster.xlsm but something like mymaster.xlsm
  • Was it possible to transfer data from *.xlsm files also
  • What if we didn’t know the number of rows of data to be transferred
  • what if the master and the other workbooks were in different folders
  • How to overcome the error: There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later? To save it on the clipboard so that you can paste it later, click yes. To delete it from the Clipboard and free memory, click No.

Watch the training video below (about 50 MB) to learn how all these queries have been properly clarified:


View the Video on YouTube.
Here is the complete VBA code solution:

Mastering Loops in Excel
Mastering Loops in Excel

Sub copyDataFromMultipleWorkbooksIntoMaster()

Dim FolderPath As String, Filepath As String, Filename As String

FolderPath = “C:\work\excel_tutorial\suppliers\”

Filepath = FolderPath & “*.xlsx”

‘To transfer data from all files you can use the wild-card character *

‘Filepath = FolderPath & “*.xls*”

Filename = Dir(Filepath)

Dim lastrow As Long, lastcolumn As Long

Do While Filename <> “”
Workbooks.Open (FolderPath & Filename)
‘Range(“A2:D2”).Copy
lastrow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row

‘If we wanted to paste data of more than 4 columns we would define a last column here also
‘lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
‘ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1),
Cells(erow, lastcolumn))
ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1),
Cells(erow, 4))

Filename = Dir

Loop

End Sub

Further reading:

Excel VBA – How do I clear the clipboard on another workbook in another application?

VBA to delete clipboard contents and proceed with VBA

Using The Clipboard In VBA

How to programmatically turn off the Clipboard warning message

Mastering Excel Macros: Looping (Book 5)

Click on the download icon and get the complete code on your desktop: