Copy data from Multiple workbooks simultaneously into One excluding first row

  • Save your "Master" workbook in the same folder where the workbooks you copy from are located.

    Open the "Master" workbook and try this code.

  • If your "Master" Workbook is saved in a different folder and you want to specify the folder where the files you want to copy from, this should work.

  • On both, I can't tell you why because I don't know what is in your folders.

    Does it get the data from all workbooks twice or just from one workbook.

    As mentioned, the workbook can not be saved in the same folder. If it is, you have to change this

    Workbooks.Open myPath & "\" & wb

    to this

    If wb <> ThisWorkbook.Name Then Workbooks.Open myPath & "\" & wb
  • Ah I see my mistake - I put the Master WB into same folder as other WBs. So all good there, thanks! :)

    As to #1: Run-time error 52. Bad file name or number. Debugging highlights line #4

  • Dear jolivanes,

    I used the first code (Save your "Master" workbook in the same folder where the workbooks you copy from are located.) and it works perfectly, except I would like to insert a transpose function as well, since the data I am copying is in rows (M3:M8) and I would like to have it in columns (A:G) - I have 140 .xlsx doc's to loop through. Can you please help me inserting the transpose function, as did not manage to do it myself (it's my first vba attempt) :)

    Thank you!


  • This request comes up fairly frequently on the forums.

    A while back I wrote a generic multi workbook copy standalone macro that can:

    1. Copy a single (specified) sheet from each workbook into a single workbook
      If no sheet name is specified, the first sheet in the workbook is assumed
    2. Optionally copy only the header from the first workbook into the output workbook
    3. Loop through all files in the input folder to build the output workbook

    The macro is in a standalone file so no need to copy a macro into another workbook.


  • Marsa,

    Please dont start a new question in an existing thread. This is known as Thread hi-jacking. Start your own thread. Its Free! If you need to, you can link back to the thread that you thought might have been helpful.

    This is from the Forum Rules.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!