Combine data from multiple workbooks into a single workbook

---

Last week I demonstrated how to combine data from multiple worksheets into a master worksheet (within the same workbook). Today, I will demonstrate how to do the exact same thing but from multiple workbooks (i.e. Spreadsheets) into a single workbook.
 
I will use the exact same data set used in my previous blog post but I will place them in two separate workbooks provided below:

st1.xlsm
File Size:	13 kb
File Type:	xlsm

Download File

---

st2.xlsm
File Size:	12 kb
File Type:	xlsm

Download File

---

The macro will run in another workbook with a tab named “Main” and it will extract the datasets from the two workbooks above.

Note that in both workbooks, the data begins from the fifth row and on that basis I have created the following code to combine the two workbooks (i.e. ST1 and ST2) into the third workbook “Main”. 

full_macro.txt
File Size:	1 kb
File Type:	txt

Download File

---

Insert the code in the textbox above in the “Module” and “This Workbook” sections in “Developer”>”Visual Basic” section of the ribbon.

​Running this macro will prompt the selection of files from which data is to be extracted into the main tab. Here is the output after the macro is executed for the two input files :

​Important notes:

• In order for this code to work, the name of the tab within the workbook in which data is combined needs to be “Main”. If the name of that particular tab is changed, the new name needs to replace the old name wherever it is used in the code above.
• Row 5 has been indicated as the first row where data is to be extracted from. This reference needs to be changed if the data (excluding the header) starts from a different point.

 

---