This seems to work. You will first need to rename your first week sheets to mon1, mon2, etc. as previously suggested.
Dim WrkSheet As Worksheet, SalesRow As Long, DayRow As Long, Category As String, i As Long
Const FirstRow = 9
Sub GetSalesData()
Application.ScreenUpdating = False
For Each WrkSheet In ActiveWorkbook.Worksheets ' Clear previous data
With WrkSheet
If .Cells(FirstRow, 1).Value = "Number" And .Cells(FirstRow + 1, 1).Value <> "" Then
.Rows(FirstRow + 1 & ":" & .Cells.SpecialCells(xlCellTypeLastCell).Row).Delete
End If
End With
Next
Sheets("Sales").Select
SalesRow = 2
Category = Cells(SalesRow, 1).Value
Do Until Cells(SalesRow, 2).Value = ""
If Cells(SalesRow, 14).Value <> "" Then ' Has a day in column N
If Cells(SalesRow, 1).Value <> "" And Cells(SalesRow, 1).Value <> Category Then
Category = Cells(SalesRow, 1).Value
End If
If Category = "ALL" Then
For i = 1 To 4
Call CopyData(Cells(SalesRow, 14).Value & i)
Next
Else
Call CopyData(Cells(SalesRow, 14).Value & Right(Category, 1))
End If
End If
SalesRow = SalesRow + 1
Loop
End Sub
Sub CopyData(DaySheet As String)
With Worksheets(DaySheet)
If .Cells(FirstRow + 1, 1).Value = "" Then
DayRow = FirstRow + 1
Else
DayRow = .Cells(FirstRow, 1).End(xlDown).Row + 1
End If
Range(Cells(SalesRow, 2), Cells(SalesRow, 6)).Copy
.Cells(DayRow, 1).PasteSpecial (xlValues)
Range(Cells(SalesRow, 20), Cells(SalesRow, 22)).Copy
.Cells(DayRow, 6).PasteSpecial (xlValues)
End With
End Sub
Blessings, and Merry Christmas!
Bookmarks