PDA

View Full Version : Combine several sheets - Union, ADO, SQL, Consolidate, Formula?



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

mancubus
10-22-2015, 07:53 AM
below macro shoud work; provided that
- worksheet names start with "Data_"
- headers are in A1:A3
- data cells are B1:B3

if not, you should amend the code.



Sub vbax_54050_consolidate_sheets()

Dim ws As Worksheet
Dim i As Long

With Worksheets("Summary")
.Cells.Clear
.Range("A1:C1").Value = Array("Name", "Age", "Town")
i = 2
For Each ws In Worksheets
If Left(ws.Name, 5) = "Data_" Then
.Range("A" & i & ":C" & i).Value = Application.Transpose(ws.Range("B1:B3").Value)
i = i + 1
End If
Next ws
End With

End Sub


worksheet names start with "Data_"
headers in A1:A3
data in B1:B3

snb
10-22-2015, 12:16 PM
Sub M_snb()
with createobject("scripting.dictionary")
.item(.count)=array("name","age","town")

for each sh in sheets
if sh.name<>"summary" then .item(.count)=application.transpose(sh.cells(1,2).resize(3))
next

sheets("summary").cells(1).resize(.count,3)=application.index(.items,0,0)
end with
End Sub