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