Combine multiple tables into one master table - same column headings

[HeyMike]

I have multiple workbooks that are very similar. One table per workbook, identical column headings on all, each workbook has rows added daily.

I need to combine each table from all workbooks into one master table so I can analyze them together. Is there a formula to set this up, or perhaps a VBA script that will combine it instead of copy and pasting tables together? Example is below...

First table/workbook:

Ticket	Job	Hours
B	job 1	5
B	job 2	5
B	job 3	5

<tbody>
</tbody>

Second table/workbook:

Ticket	Job	Hours
A	job 1	3
A	job 2	3

<tbody>
</tbody>

This is the combined output I need to produce into a new workbook:

Ticket	Job	Hours
B	job 1	5
B	job 2	5
B	job 3	5
A	job 1	3
A	job 2	3

<tbody>
</tbody>

 

[hippiehacker]

I have multiple workbooks that are very similar. One table per workbook, identical column headings on all, each workbook has rows added daily.

I need to combine each table from all workbooks into one master table so I can analyze them together. Is there a formula to set this up, or perhaps a VBA script that will combine it instead of copy and pasting tables together? Example is below...

First table/workbook:

Ticket	Job	Hours
B	job 1	5
B	job 2	5
B	job 3	5

<tbody>
</tbody>

Second table/workbook:

Ticket	Job	Hours
A	job 1	3
A	job 2	3

<tbody>
</tbody>

This is the combined output I need to produce into a new workbook:

Ticket	Job	Hours
B	job 1	5
B	job 2	5
B	job 3	5
A	job 1	3
A	job 2	3

<tbody>
</tbody>

would be good to know which excel version if 2007 or later maybe Consolidate might be a choice??

http://office.microsoft.com/en-001/excel-help/consolidate-data-in-multiple-worksheets-HP010095249.aspx#BMconsolidate_by_position

otherwise a vba would be best.

 

[Andrew Poulsom]

You should find what you need here:

Copy/Paste/Merge examples

 

[hippiehacker]

this could be a vba code assuming range is A:C otherwise change the range and activesheet is the 'mastersheet' where data needs to be collected

Sub heymike()
For Each sh In Worksheets
 If sh.Name <> ActiveSheet.Name Then
  With sh
   .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
  End With
 End If
Next sh
End Sub