Split Data based on Column Headings with Two Criteria Month Column and Store Column

[AVALADEZ75]

Hello,

I am trying to split data that has a month column and store columns. I want to be able to split the data into different worksheets based on store columns and have it show the totals by months.

Months	Store 1	Store 2
12	$ 1,000,000.00	$ 200,000.00
4	$ 1,000,000.00	$ 200,000.00
1	$ 1,000,000.00	$ 200,000.00
2	$ 1,000,000.00	$ 200,000.00
8	$ 1,000,000.00	$ 200,000.00
11	$ 1,000,000.00	$ 200,000.00
6	$ 1,000,000.00	$ 200,000.00
10	$ 1,000,000.00	$ 200,000.00
3	$ 1,000,000.00	$ 200,000.00
7	$ 1,000,000.00	$ 200,000.00
9	$ 1,000,000.00	$ 200,000.00
5	$ 1,000,000.00	$ 200,000.00

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> </colgroup><tbody>
</tbody>

Master Sheet

below I would like the sheets to be named after each column "Store #" and have

Months	Store 1
12	$ 1,000,000.00
4	$ 1,000,000.00
1	$ 1,000,000.00
2	$ 1,000,000.00
8	$ 1,000,000.00
11	$ 1,000,000.00
6	$ 1,000,000.00
10	$ 1,000,000.00
3	$ 1,000,000.00
7	$ 1,000,000.00
9	$ 1,000,000.00
5	$ 1,000,000.00

<colgroup><col><col></colgroup><tbody>
</tbody>

Let me know if you can help.

 

[Comfy]

In your mastersheet will you have duplicate months?

 

[AVALADEZ75]

The master sheet will remain as is. I am hoping that a macro can take the month column and each individual store column and seperate them into their own sheets. That way the excel work book will have the Master, Store1, Store 2, store 3, etc and in the store worksheets it will show Column A Month, Column B Store 1 and the data.

 

[Comfy]

I have assumed that your data start in Cell A1:

Excel 2010
	A	B	C
1	Months	Store 1	Store 2
2	12	$ 1,000,000.00	$ 200,000.00
3	4	$ 1,000,000.00	$ 200,000.00
4	1	$ 1,000,000.00	$ 200,000.00
5	2	$ 1,000,000.00	$ 200,000.00
6	8	$ 1,000,000.00	$ 200,000.00
7	11	$ 1,000,000.00	$ 200,000.00
8	6	$ 1,000,000.00	$ 200,000.00
9	10	$ 1,000,000.00	$ 200,000.00
10	3	$ 1,000,000.00	$ 200,000.00
11	7	$ 1,000,000.00	$ 200,000.00
12	9	$ 1,000,000.00	$ 200,000.00
13	5	$ 1,000,000.00	$ 200,000.00
Sheet1

This will do as you asked:

Sub Split_Stores()
Dim i As Long, j As Long
Dim LastRow As Long, LastCol As Long
Dim ws1 As Worksheet, ws2 As Worksheet


Set ws1 = ActiveSheet


LastRow = ws1.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastCol = ws1.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Column


For i = 2 To LastCol
Set ws2 = Sheets.Add
ws2.Move After:=Sheets(Sheets.Count)
ws2.Name = ws1.Cells(1, i)
    For j = 1 To LastRow
        ws2.Cells(j, 1) = ws1.Cells(j, 1): ws2.Cells(j, 2) = ws1.Cells(j, i)
    Next j
Next i


End Sub