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:
[TABLE="width: 190"]
<tbody>[TR]
[TD="align: center"]Ticket[/TD]
[TD="align: center"]Job[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 1[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 3[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

Second table/workbook:
[TABLE="width: 190"]
<tbody>[TR]
[TD="align: center"]Ticket[/TD]
[TD="align: center"]Job[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]job 1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]job 2[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

This is the combined output I need to produce into a new workbook:
[TABLE="width: 190"]
<tbody>[TR]
[TD="align: center"]Ticket[/TD]
[TD="align: center"]Job[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 1[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 3[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]job 1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]job 2[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:
[TABLE="width: 190"]
<tbody>[TR]
[TD="align: center"]Ticket[/TD]
[TD="align: center"]Job[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 1[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 3[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

Second table/workbook:
[TABLE="width: 190"]
<tbody>[TR]
[TD="align: center"]Ticket[/TD]
[TD="align: center"]Job[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]job 1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]job 2[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

This is the combined output I need to produce into a new workbook:
[TABLE="width: 190"]
<tbody>[TR]
[TD="align: center"]Ticket[/TD]
[TD="align: center"]Job[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 1[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]job 3[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]job 1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]job 2[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

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