Creating auto updating Summary Sheet

[baldymon]

Hi, first post, so hopefully have adhered to all forum rules.

I have been looking to create a summary sheet from data that exists in other sheets (within the same workbook). Searched and searched and came across a Excel Forum thread which had the following macro in a book1.xls file: -

Please Login or Register  to view this content.

This macro seemed to perform the job I needed as each tab (varying number of tabs) has a list (in the same format) but differing lengths and this macro seemed to append to the data as it went throug the tabs, and the summary table was updated automatically as data was populated on the other tabs, or as tabs were inserted. It seemed to only work for the first 10 columns.

I thought it was ideal and copied it down without noting the thread number or the title.

As a novice, what I also failed to find out was: -

1) If I add this macro to another workbook, how do I configure the workbook to be able to run this macro? (dummies guide would be good)
2) How can I modify this macro to look at specific columns and that are spread over the worksheet e.g. column A, D, F, X, Y etc? (I could re-arrange the data, but for asthetic reasons would rather not).
3) Is there any easier method for creating a summary sheet of data of an inderminable size and spread across an undeterminable number of tabs?
4) I would also like to add extra information on the summary sheet for columns not copied across (but based on the copied data) so that calculations and eventually a self configuring pivot table can be created. Can this be achieved easily?

Not much to ask, I know but if anyone can help, it would be appreciated.

Thanks

[baldymon]

Attached is the file I downloaded (although modified so it now doesn't seem to work correctly), but gives the gist of what I was tryint to do.

Compiling table from data in other tabs.xls

[baldymon]

Think I may have solved most of this by looking at other threads.

1) If I add this macro to another workbook, how do I configure the workbook to be able to run this macro? (dummies guide would be good)

Answer: Routine must be called "Private Sub Worksheet_Activate()" and inserted into the worksheet using the "View Code" option when right clicking the Tab.

2) How can I modify this macro to look at specific columns and that are spread over the worksheet e.g. column A, D, F, X, Y etc? (I could re-arrange the data, but for asthetic reasons would rather not).
3) Is there any easier method for creating a summary sheet of data of an inderminable size and spread across an undeterminable number of tabs?

Answer: I've found the following code useful.

Please Login or Register  to view this content.

4) I would also like to add extra information on the summary sheet for columns not copied across (but based on the copied data) so that calculations and eventually a self configuring pivot table can be created. Can this be achieved easily?

This is the bit I haven't figured out yet, how to add data to a summary sheet of varying size in order to automatically build a Pivot chart.

Basically I hve a series of equipment and projects (on different tabs) that require capacity calculations. I can copy the data for each monthly requirement across from multiple projects and equipment to a summary sheet. The next step is to add the cacluation for the capacity and insert it into a pivot chart for display to show capacity of all items of equipment across multiple projects. This is the bit I don't understand how to do, other than manually after the creation of the summary sheet.

Any help on this 4th item would be welcome.

[baldymon]

Think I may have solved most of this by looking at other threads.

1) If I add this macro to another workbook, how do I configure the workbook to be able to run this macro? (dummies guide would be good)

Answer: Routine must be called "Private Sub Worksheet_Activate()" and inserted into the worksheet using the "View Code" option when right clicking the Tab.

2) How can I modify this macro to look at specific columns and that are spread over the worksheet e.g. column A, D, F, X, Y etc? (I could re-arrange the data, but for asthetic reasons would rather not).
3) Is there any easier method for creating a summary sheet of data of an inderminable size and spread across an undeterminable number of tabs?

Answer: I've found the following code useful.

Please Login or Register  to view this content.

4) I would also like to add extra information on the summary sheet for columns not copied across (but based on the copied data) so that calculations and eventually a self configuring pivot table can be created. Can this be achieved easily?

This is the bit I haven't figured out yet, how to add data to a summary sheet of varying size in order to automatically build a Pivot chart.

Basically I hve a series of equipment and projects (on different tabs) that require capacity calculations. I can copy the data for each monthly requirement across from multiple projects and equipment to a summary sheet. The next step is to add the cacluation for the capacity and insert it into a pivot chart for display to show capacity of all items of equipment across multiple projects. This is the bit I don't understand how to do, other than manually after the creation of the summary sheet.

Any help on this 4th item would be welcome.