Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o></o>
<o></o>
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></o>
<o></o>
The set-up: <o></o>
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></o>
<o></o>
What I want to achieve:<o></o>
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></o>
<o></o>
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></o>
<o></o>
Could somebody help me with the VBA code for that? Unfortunately I am not that VBA-mature yet to create it myself…
<o></o>
<o></o>
One thing: our company still uses MS Excel 2003<o></o>
fficeffice" /><o></o><o
></o><o
></o>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
></o><o
></o>The set-up: <o
></o>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
></o><o
></o>What I want to achieve:<o
></o>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
></o><o
></o>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
></o><o
></o>Could somebody help me with the VBA code for that? Unfortunately I am not that VBA-mature yet to create it myself…
<o
></o> <o
></o>One thing: our company still uses MS Excel 2003
<o></o>
