Transfer Data From Multiple Workbooks To A New Workbook

Welcome to Excel Jet Consult blog post. In this post, we will learn how to write VBA code that will automatically pull data from different workbooks and sheets to a new workbook. Let’s get started

The first thing we want to do is put the two Excel workbooks into a new folder. Below is the caption of the two Excel workbooks in a folder named Excel

We want to open the first Excel Workbook named AbujaReport to see how many sheets are in it. From the caption below, we can see that there is one sheet named Sales Data

Secondly, we want to open the other workbook named Emp. We can see that there are five sheets in the workbook according to the caption below.

Having see the contents in both Excel workbooks, we want to close the workbooks and open a brand-new Excel workbook

To open the Visual Basic Editor, press ALT + F11.

In the Insert tab of the VBA editor, click on Module

In the code window, execute the following lines of code as seen in the caption below

What the code above simply implies is that we want to loop through to fetch all the Excel workbooks inside the Excel folder and copy all the sheets into a new sheet located in the new Excel workbook

Finally, click on F5 to run the code. All the sheets from the two Excel workbooks has been transferred into the summary sheet as seen in the caption below


Totally amazing


Posted in Blog.

Leave a Reply

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