I'm trying to group a pivot table by dates grouped in Months/Years. I have found code online which seems simple enough, and when I test it, I get no errors, but nothing happens in the workbook/Pivot Table. Can anybody see what is wrong?
Sub CreatePivotTable()
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
On Error Resume Next
Application.DisplayAlerts = False
'Worksheets("PivotTable").Delete
SheetExists = WorksheetExists("PivotTable")
If Not SheetExists = True Then
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
End If
Application.DisplayAlerts = True
Set PSheet = ActiveWorkbook.Worksheets("PivotTable")
Set DSheet = ActiveWorkbook.Worksheets("Transactions") '''Source Data Sheet Name'''
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
PTableExists = ExistPivot("PivotTable")
If PTableExists = True Then
'MsgBox "Deleting PTable!"
Call DeletePivotTable("PivotTable", "PivotTable")
End If
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PivotTable")
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")
'https://docs.microsoft.com/en-us/office/vba/api/excel.pivotfield.orientation
With ActiveSheet.PivotTables("PivotTable").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
''This line is the part I'm working on, I have moved it around and tried everything, no errors, but no results!
PTable.PivotFields("Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
End Sub