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.
How the data is now. Example Sheet 1:
Excel 2010
<TBODY>
</TBODY>
How the data is now. Example Sheet 2:
Excel 2010
<TBODY>
</TBODY>
How I want the Master Tab to look:
Excel 2010
<TBODY>
</TBODY>
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.
Code:
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