Automatic pull of data from the same sheets in different workbooks into a master sheet in another workbook

TT23

New Member
Joined
Jun 6, 2012
Messages
1
Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
<o:p></o:p>
I’ve spent 2 days on forums and messageboards trying to look for the solution to my specific case and finally decided to post it as a question. Any help will be much appreciated.<o:p></o:p>
<o:p></o:p>
The set-up: <o:p></o:p>
I have several source workbooks with different names. All of them are in a one folder, together with other files. The folder is on the SharePoint, if that makes any difference. Each workbook contains many sheets, but one sheet is always present and has name “Datasheet”. This sheet “Datasheet” contains the same columns A to R, but the number of rows varies from workbook to workbook (e.g. workbook1 has now 1607 rows, workbook2 3834 rows, etc.). What’s more, the “Datasheets” in workbooks are being updated on a regular basis, i.e. number of rows can change (decrease or increase). In order to keep the same kind of formatting and formulas the range of data in each “Datasheet” is organised as a list.<o:p></o:p>
<o:p></o:p>
What I want to achieve:<o:p></o:p>
I want to be able to consolidate the data from each of the “Datasheet” from the source workbooks in a master datasheet in another master workbook. By consolidate I mean to have first all 1607 rows from workbook 1, then all 3834 rows from workbook 2 etc. The issue is: I want that master datasheet to automatically append the new rows if they are added in the “Datasheet” in any of the source workbooks. If it wasn’t for these new rows, there wouldn’t have been a problem, since I would just paste the specific ranges from each “Datasheet” as links in the master datasheet one after another and that’d be it. <o:p></o:p>
<o:p></o:p>
The ultimate goal of that exercise is to be able to build a normal pivot-table on the master datasheet to be able to play with all the columns and so that this pivot table automatically includes all the new rows.<o:p></o:p>
<o:p></o:p>
Could somebody help me with the VBA code for that? Unfortunately I am not that VBA-mature yet to create it myself…
<o:p></o:p>
<o:p></o:p>
One thing: our company still uses MS Excel 2003 :rolleyes:<o:p></o:p>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,211,789
Messages
6,103,985
Members
447,888
Latest member
eaydogan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top