0

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
1

So I was following this --> https://stackoverflow.com/questions/45279335/excel-vba-to-group-by-month-and-year-for-all-pivot-tables-on-activesheet

pPF.LabelRange.Group.Cells(2, 1) _
Start:=True, End:=True, Periods:= _
    Array(False, False, False, False, True, True, True)

but on another site I saw this --> https://answers.microsoft.com/en-us/msoffice/forum/all/vba-pivot-table-groups-dates-yearsquartersmths-etc/15754480-d386-41ac-a5d5-3d724a0f3e51

rngGroup.Cells(1).Group Periods:=Array(False, False, False, True, True, True, False)

The issue was that I had to use Cells(1) but more importantly, it goes BEFORE .Group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.