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:

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

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

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


    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. Syakirah

    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. Pingback: Automate Copying Excel Column Data from Sheet1 to Sheet2 with VBA | Excel VBA Training Videos

  4. Sivaraju

    Hi Dinesh,

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


  5. Clare Smith

    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.


  6. Amit

    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. sorin

      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

  7. Joao Nascimento

    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

  8. Jigar

    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.


  9. Teresa

    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!

  10. Chong

    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.

  11. sorin

    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.

  12. sorin

    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

  13. George Filippaios

    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?

  14. Nithya

    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.

  15. Mohideen

    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.

  16. Ken McMillan

    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?


Leave a Reply

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