I have an information with a data frame (ex: 01.01.2021 - 27.01.2021), and I need to classify it in groups (ex.: W1, W2, W3, W4), then in each group classify in subgroups (Day 1, day 2, day 3, day 4 and day 5), then count it.
Ps: As there are 7 (monday to sunday) different dates for 5 groups (week numbers), and the data from day 5 and 6 of each week should be combined
Sample
'
' Organize Macro
' Arranges data from Excel
'
Workbooks.Open Filename:= _
"C:UsersGXG9623OneDrive - International Flavors & Fragrances Inc-DocumentsSAPSAP GUITeste.xls"
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("B9").Select
Rows("3:3").Select
Rows("2:3").Select
Range("A3").Activate
Range("2:3,1:1").Select
Range("A1").Activate
Selection.Delete Shift:=xlUp
Columns("A:D").Select
Selection.Delete Shift:=xlToLeft
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A1:B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Teste!R1C1:R815C35", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Pstng Date"), "Count of Pstng Date", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Pstng Date")
.Orientation = xlRowField
.Position = 1
End With
End Sub
Sub Present()
'
' Present Macro
' Fits the data into KPI AP Brazil
Workbooks.Open Filename:= _
"S:ND_P2PLatin America13 - BrazilKPI - AP Brazil.xlsx"
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""04.01.2021"")"
Range("H5").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""05.01.2021"")"
Range("I5").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""06.01.2021"")"
Range("J5").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""07.01.2021"")"
Range("K5").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""08.01.2021"")"
Range("G6").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""11.01.2021"")"
Range("H6").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""12.01.2021"")"
Range("I6").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""13.01.2021"")"
Range("J6").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""14.01.2021"")"
Range("K6").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""15.01.2021"")"
Range("K7").Select
ThisWorkbook.Save
End Sub```
The goal is to reference the data based on the dates. I.E.: 5 invoices processed on Week 1, day 4.