VBA is refusing to use "dd/mm/yyyy" for VBA code for grouping by date.
Macro1() shows me recording grouping by date by seven days starting from the 6th of June 2021 to the 4th of December 2021 on a pivot table.
Macro11() is me adding code to filter the pivot by date between the 6th of June 2021 to the 4th of December 2021 and changing the date serial number in the earlier recorded macro to proper dates.
For the date between filter or whenever I have used dates in past VBA code I have always used "dd/mm/yyyy" and had no issues but for the grouping code I had to use "mm/dd/yyyy" for the start/end dates. If I use "dd/mm/yyyy" I get an error when running the code that the end number must be greater than the start number, which I assume is the code thinking I put start 6th of June 2021 to end 12th of April 2021.
Both my Windows system region settings and my Excel cell format setting are set to use "dd/mm/yyyy" so I am not sure why the grouping code won't.
VBA Code:
Sub Macro1()
'
' Macro1 Macro
'
'
Selection.Group Start:=44353, End:=44534, By:=7, Periods:=Array(False, _
False, False, True, False, False, False)
End Sub
Sub Macro11()
'
' Macro1 Macro
'
'
Selection.PivotTable.PivotFields("Date").ClearAllFilters
Selection.PivotTable.PivotFields("Date").PivotFilters.Add2 _
Type:=xlDateBetween, Value1:="06/06/2021", Value2:="04/12/21"
Selection.Group Start:="06/06/2021", End:="12/04/2021", By:=7, Periods:=Array(False, _
False, False, True, False, False, False)
End Sub
Macro1() shows me recording grouping by date by seven days starting from the 6th of June 2021 to the 4th of December 2021 on a pivot table.
Macro11() is me adding code to filter the pivot by date between the 6th of June 2021 to the 4th of December 2021 and changing the date serial number in the earlier recorded macro to proper dates.
For the date between filter or whenever I have used dates in past VBA code I have always used "dd/mm/yyyy" and had no issues but for the grouping code I had to use "mm/dd/yyyy" for the start/end dates. If I use "dd/mm/yyyy" I get an error when running the code that the end number must be greater than the start number, which I assume is the code thinking I put start 6th of June 2021 to end 12th of April 2021.
Both my Windows system region settings and my Excel cell format setting are set to use "dd/mm/yyyy" so I am not sure why the grouping code won't.