VBA - Excel 2010-Looping thr WS and combining them into one Master Tab when all columns not in the same place accross the tabs

[CharBram]

Guys, I need some help consolidating the data from a bunch of tabs into one Master tab

The macro needs to:
-Create a "Master" tab, if it does not already exist.
-Type "Business Unit" and "Product Type" into A1 and B1 of the Master Tab. This data is in column A and B of each WS
-Will then need to "read" the header of columns C-G so it can identify if that header already exists in the target Master Tab.
-Will then copy the data from each worksheet into the correct corresponding column of the master worksheet, creating a flat file.

I am stuck trying to come up with an approach to do this in VBA.

Does anyone have any experience with this or ideas?

I found some code below that loops through each tab but doesnt worry about the column header.

Sub CopyIt()
    Dim WS As Worksheet
    Application.ScreenUpdating = False
    For Each WS In ActiveWorkbook.Worksheets
        If WS.Name <> "Master" Then
            WS.Range("A2:").Copy
            Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
    Next
    Application.ScreenUpdating = True
End Sub

How the data is now. Example Sheet 1:

Excel 2010

	A	B	C	D	E
1	Business Unit	Product Type	Price Plan 1	Price Plan 2	Price Plan 3
2	AAA-BBB	Product 1	0.722	0.368	0.079
3	AAA-BBB	Product 2	0.247	0.174	0.227
4	AAA-BBB	Product 20	0.532	0.451	0.909
5	AAA-BBB	Product 45	0.339	0.169	0.402
6	AAA-BBB	Product AA1	0.775	0.211	0.483
7	AAA-BBB	Product BB2	0.837	0.393	0.967
8	AAA-BBB	Product C3	0.577	0.251	0.641
9	AAA-BBB	Product QQ	0.461	0.147	0.153
10	AAA-BBB	Product NCC	0.110	0.746	0.108

<TBODY>
</TBODY>

Sheet2​

How the data is now. Example Sheet 2:
Excel 2010

	A	B	C	D	E
1	Business Unit	Product Type	Price Plan 1	Price Plan 4	Price Plan 5
2	AAA-CCC	Product 1	0.214	0.970	0.044
3	AAA-CCC	Product 2	0.844	0.904	0.459
4	AAA-CCC	Product 20	0.062	0.658	0.572
5	AAA-CCC	Product 45	0.200	0.005	0.448
6	AAA-CCC	Product AA1	0.618	0.589	0.716
7	AAA-CCC	Product BB2	0.929	0.703	0.230
8	AAA-CCC	Product C3	0.519	0.302	0.896
9	AAA-CCC	Product QQ	0.850	0.644	0.922
10	AAA-CCC	Product NCC	0.982	0.883	0.971

<TBODY>
</TBODY>

Sheet1​

How I want the Master Tab to look:

Excel 2010

	A	B	C	D	E	F	G
1	Business Unit	Product Type	Price Plan 1	Price Plan 2	Price Plan 3	Price Plan 4	Price Plan 5
2	AAA-BBB	Product 1	0.722	0.368	0.079
3	AAA-BBB	Product 2	0.247	0.174	0.227
4	AAA-BBB	Product 20	0.532	0.451	0.909
5	AAA-BBB	Product 45	0.339	0.169	0.402
6	AAA-BBB	Product AA1	0.775	0.211	0.483
7	AAA-BBB	Product BB2	0.837	0.393	0.967
8	AAA-BBB	Product C3	0.577	0.251	0.641
9	AAA-BBB	Product QQ	0.461	0.147	0.153
10	AAA-BBB	Product NCC	0.110	0.746	0.108
11	AAA-BBB	Product 1	0.214			0.970	0.044
12	AAA-BBB	Product 2	0.844			0.904	0.459
13	AAA-BBB	Product 20	0.062			0.658	0.572
14	AAA-BBB	Product 45	0.200			0.005	0.448
15	AAA-BBB	Product AA1	0.618			0.589	0.716
16	AAA-BBB	Product BB2	0.929			0.703	0.230
17	AAA-BBB	Product C3	0.519			0.302	0.896
18	AAA-BBB	Product QQ	0.850			0.644	0.922
19	AAA-BBB	Product NCC	0.982			0.883	0.971

<TBODY>
</TBODY>

Master​

 

[CharBram]

Problem Solved!

I found this post which solved my issue perfectly. Didn't even have to make any code modifications!

http://www.mrexcel.com/forum/excel-...solidate-multiple-sheets-varying-columns.html