September 10, 2014

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)
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

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


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:

26 thoughts on “How to transfer multiple rows of data from multiple workbooks into master workbook with VBA

  1. Dear Sir

    Your teachings always help million of excel learners. I am very much impressed with your way of teaching. I am 58 years of old and started working in computer very late but when one of my friend told me about your lessons. It impressed me and created an eagerness to learn VBA commands. Since then I always try to learn excel with the help of your classes With the help of ur classes I am able to do lot of work which I used to think as impossible task in excel.

    Sir I am working in a private company I have 1500 regular and daily paid workers.I am maintaining their Provident fund data in excel sheet. I had to issue PF statement every year to each employee.

    I maintain data in Sheet1 of excel file in columns Empl_code, Empl_Name, Salary_Month, Basic+DA, Empl Provident Fund Deduction, Company_Contribution to PF etc. 12 rows for 12 month data for each employee thus data is in 1500*12 rows in my Sheet1. In Sheet2 I have formed a report wherein extract data from Sheet1 with the help of Index & Match formula. Thus I am able to generate statement of each employee one by one. I enter Empl-No in the report and data of particular employee for 12 month appears in the statement. Then I print the statement. By this method I am able to print statement of 1500 employees one by one which is tiring and boring job.

    Can u help me to generate & print statements of all 1500 employees with the help of VBA command so that I may be able to print statement at one go.

    Hope u might have understood my problem and expect help in this regard soon

    with regrds


  2. Hi Sir,
    I’m a real beginner who needs to perform task using excel.

    Here’s my problem:
    There are plenty of files directory that own respective .xls files. I need to pull selected data from .xls files into a single master workbook with a condition. This should be run automatically by clicking button ‘run’. So, functions will read those condition and pull only data with the condition from those .xls files from different files directory.

    For example:
    File A , File B , File C and File D

    File A contain one.xls , two.xls and three.xls
    File B contain four.xls , five.xls and six.xls
    File C contain seven.xls , eight.xls
    File D contain nine.xls and ten.xls

    I have to pull the data from every rows in all .xls file which are month, date, name, platform, event and file name if row meet condition. The condition should be only ‘no report generated’ then it pull data automatically into Main workbook(master workbook). Else it will capture as ‘Nil’.

  3. Hi Dinesh,

    Can you please provide your excel sheets for my reference. It would be great helpful if you could do that.


  4. Hello Dinesh,

    I observe that your code above did not define the .xlsm file i.e. the master file (mymasterfile.xlsm) where the data from the other worksheets should be copied into. When I tried to run the code without the name of the master file showing anywhere in the code, nothing was transferred over. I wonder if this was inadvertently omitted.Could you kindly clarify and help with this, please.

    Thanks for all your brilliant tutorials.


  5. Hello sir, I am getting run time error 424 while running the line
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    1. Hello Armit.

      I am Sorin and I face the same issue as you did.
      Have you received the solution?
      If yes, please give me a hand.
      Thank you

  6. Hello sir,

    I’m amazed with your website, but haven’t found yet a way to transfer multiple rows of data from multiple worksheets into one worksheet and export as PDF with VBA.

    I need this to send data to the clients of the company i work.

    I hope you can help me with this.

    Best regards

  7. Hello Sir,

    Thanks for your wonderful video and explanation. It is indeed very helpful to everyone out here.

    I have one query on the above subject.

    I am following all the above steps, but while running the macros, content from other excel is not getting copied to Master excel. What could the possible reason ?

    Appreciate your help on this.


  8. Thank you for posting the videos!

    I’d like to create a master file that contains monthly totals of different costs from multiple workbooks. Each workbook is from a different location. Each workbook has a sheet for each month.

    For example, Location X had 10 transactions in January; hence, there are 10 rows of cost data. Location Y had 8 transactions in January; hence, there are 8 rows of cost data. There are 6 types of costs for each transaction \; hence, there are 6 columns. The number of transactions vary monthly. I’d like to create a master file that shows the total for each of the 6 types of costs from all of the transactions for location X, and the total for each of the 6 types of costs from all of the transactions for location Y.

    Similar to your example, I’d like to transfer data of multiple rows into a master file but I’d like the data already summed up when it appears on the master file. Files from each location are xlsm files.

    Thanks in advance!

  9. Hi sir, is it possible to write VBA code to prevent double copy and paste data from the same file name each time run the program? For example, the data from suppllier a, b, c previously already copied in the master file, now I want to add in the data from supplier d, e, f, etc., but when click on run in the master file, I don’t want the data from supplier a,b and c to be added into the master file again.

    Hope you can help me on this.

    Thank you very much.

  10. Hello sir.
    I need your help.
    I’ve replicated your exercises from:
    unfortunately it does not work.
    I get the following message:
    Run-time error ‘424’. Object required.
    when debugging code line is yellow:
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    What to do?.
    Thank you.

  11. Sorry but I don’t know what to do. It does not work. I spent a lot a time to figure what to solve it. I failed each time.
    Bellow is the code

    Sub CopyData()
    Dim FolderPath As String, FilePath As String, FileName As String
    FolderPath = “\confidential\”
    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, 1).End(xlUp).Row
    lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Cells(2, 1), 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, 1), Cells(erow, 6))
    FileName = Dir
    Application.DisplayAlerts = True
    End Sub

    Error I receive:
    Run-time error ‘424’: Object required.
    after debugging I am indicated that the error is here:
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

  12. I would like first to say, what a great job you are doing and is very helpful.
    I was watching the example and I was wondering if you can extract info from one workbook to another under a condition?
    What I mean is that, is it possible for an updated workbook to fill only the new data to the other workbook?

  13. Hi Sir,

    I have a strange requirement :

    The client has a report with two tabs. The client wants to copy the data of the second tab to the first tab when a condition is satisfied. But the second tab has multiple tables. So for example if condition a is satisfied then it has to go to the second tab and pull the table A of the second tab and populate in the first tab of the worksheet. If condition b is satisfied then it has to go to the second tab and pull the table B of the second tab and populate in the first tab of the worksheet and so on.. how can I do this in excel

    Please help me sir . Its an urgent requirement.

  14. Hi Sir,
    I hope you are doing well.
    I have multiple excel file in different location with Password protection (Each Diffrent Password). End of the day i want to copy paste data in one master file. All file data Headers Same. Every time i have to enter password and open file for copy paste to Master file. Kindly help me out automated Macro VBA code for this.

  15. Dear Dinesh,
    I watched your excellent video on how to transfer multiple workbooks into a main workbook. I tried with my project as I am copying columns in six different sheets and trying to put each side by side in my master. I get an error on this line of code:
    ActiveSheet.Paste Destination = Worksheets(“Sheet1”).Range(Cells(erow, 1), Cells(erow, 4))
    I’m not concerned with rows, but just the columns in each worksheet I want to copy to the master and have them all side by side.
    Can you please tell me what I’m doing wrong?

  16. Hi, i was able to use this successfully but I have one more issue. How do we get this to also loop through Sub folders? Some of the files i need to pull from are in the main folder and others are in sub folders. How would the above code be edited to go through the sub folders as well?

  17. many many thanks to you i have seen both of the videos releted to this topic it is really wonderful and it working for me,,,, already i am implementing this in my office.

  18. Hello sir!

    Nice tips. I have a master file where I want the range F76:U796 to be copied from multiple workbooks in the same folder. I have used the following:

    Sub copyDataFromMultipleWorkbooksIntoMaster()
    Dim FolderPath As String, Filepath As String, Filename As String

    FolderPath = “D:\Aircrew_Flying_Hour\MASTER_CALCULATOR\”

    Filepath = FolderPath & “*.xls*”

    Filename = Dir(Filepath)

    Do While Filename “”
    Workbooks.Open (FolderPath & Filename)

    Range(“F76 : U796”).Copy

    lastrow = ActiveSheet.Cells(Rows.Count, 6).End(xlUp).Row
    lastColumn = ActiveSheet.Cells(77, Columns.Count).End(xlToLeft).Column

    Range(Cells(77, 6), Cells(796, 21)).Copy

    ApplicationDisplayAlerts = False

    erow = Sheet1.Cells(Rows.Count, 1).End(xlrup).Offset(1, 0).Row
    ActiveSheet.pasteDestination = Worksheets(“sheet15”).Range(Cells(erow, 1), Cells(erow, 4))

    Filename = Dir

    ApplicationDisplayAlerts = False
    End Sub

    Please tell me what did I do wrong. The code does not run. All my source files are .xlsx


  19. Hi Dinesh. Thank you so much for your video! I have a different situation that I would like your help with. The Suppliers file in my case has a number of sheets but the information I want to copy to my master file is on one and same sheet in every suppliers file. Also my master file has a number of sheets as well but I only need to copy all the data from the suppliers file into the raw data sheet of the Master. How do I write the VBA code to select the particular sheet and copy automatically to the raw data sheet or my master file?

  20. Dear Sir,
    I am very much new to VBA, I had been working in procurement. we had a master database in Excel, where we maintain part numbers and its related suppliers,cost for the part numbers, and added value in sheet 1 and in sheet 2 we maintain the bill of material costs for the part number. So we had like around 30 suppliers and 1000 part numbers and each part number will have 30 bill of materials.

    So the suppliers send us the cost break downs every quarter with prices for the bill of materials in one sheet and total price, added value in another sheet. when the prices are changed, we have to update the prices from the supplier cost break down the sheet to master file for that particular part number. Could you please guide me with solution for my problem.

    Question- When we receive a cost break down sheet from a supplier, The added value from the CBD(Cost .break down) should update the value in master file by filtering the supplier name and part number. Similarly, the Bill of materials from the CBD work book sheet 2 should update the prices for the same part number in sheet 2 of master file(work book)

  21. Hi Sir,

    Thanks for the teaching and the video. I am able to run the VBA code fine today I had one master file and 3 other files with the date which when I run the VBA code i can get the data in the master file but I have a few questions.
    1) if I run this command tomorrow it should only take the new entries from all the 3 .xlsx files which are the current date and get copied to the master file.
    2) i also need all the formating in each row when i get the data in the master file. currently, I am not getting the formating in the master file.

Comments are closed.