Copy data from Multiple workbooks simultaneously into One excluding first row

  • okunamatata

    Changed the title of the thread from “Copy data from Multiple workbooks into One excluding first row” to “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

    Code
    1. Workbooks.Open myPath & "\" & wb

    to this

    Code
    1. If wb <> ThisWorkbook.Name Then Workbooks.Open myPath & "\" & wb
  • 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!

    Marsa

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


    MultiWorkbookCopy.xlsm

  • royUK

    Closed the thread.