theta
10-20-2015, 11:18 PM
I have been working on a method of consolidating customer data. This data is used to create a summary table. Rather than have one 'live' table, I use source data to create a table, so that it remains flexible (and individual sheets can be exported to other workbooks). The summary table is then re-produced.
I am hoping to automate this process so that I can run a macro that will loop through all sheets and produce a summary table. The desired output would work as follows:
Worksheets (x10)
Heading in column A
Data in column B
These worksheets are all name 'Data_Sheet1' 'Data_Sheet2' etc (Data_ is the convention)
Summary
Heading along Row 1
Data consolidated below headings
I am not sure what is the best process for consolidating this data - union, ADO, SQL, consolidate, or a table made of formulas produced using a macro?
An example:
Data_Sheet1
Name...David
Age....24
Town...Reading
Data_Sheet2
Name...Peter
Age....30
Town...Cambrian
Data_Sheet3
Name...Michael
Age....40
Town...Brecon
Summary
Name......Age....Town
David.....24.....Reading
Peter.....30.....Cambrian
Michael...40.....Brecon
How can this be acheived? It will likely use arrays and loops to form a table, or an SQL/ADO style approach. Struggling with the code as very rusty.
Windows 7
Excel 2003
Excel 2007
I am hoping to automate this process so that I can run a macro that will loop through all sheets and produce a summary table. The desired output would work as follows:
Worksheets (x10)
Heading in column A
Data in column B
These worksheets are all name 'Data_Sheet1' 'Data_Sheet2' etc (Data_ is the convention)
Summary
Heading along Row 1
Data consolidated below headings
I am not sure what is the best process for consolidating this data - union, ADO, SQL, consolidate, or a table made of formulas produced using a macro?
An example:
Data_Sheet1
Name...David
Age....24
Town...Reading
Data_Sheet2
Name...Peter
Age....30
Town...Cambrian
Data_Sheet3
Name...Michael
Age....40
Town...Brecon
Summary
Name......Age....Town
David.....24.....Reading
Peter.....30.....Cambrian
Michael...40.....Brecon
How can this be acheived? It will likely use arrays and loops to form a table, or an SQL/ADO style approach. Struggling with the code as very rusty.
Windows 7
Excel 2003
Excel 2007