Estou tentando agrupar uma tabela dinâmica por datas agrupadas em Meses/Anos. Encontrei um código online que parece bastante simples e, quando o testo, não recebo erros, mas nada acontece na pasta de trabalho/tabela dinâmica. Alguém pode ver o que está errado?
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
Então, eu estava seguindo isso -> https://stackoverflow.com/questions/45279335/excel-vba-to-group-by-month-and-year-for-all-pivot-tables-on-activesheet
mas em outro site eu vi isso --> https://answers.microsoft.com/en-us/msoffice/forum/all/vba-pivot-table-groups-dates-yearsquartersmths-etc/15754480-d386-41ac- a5d5-3d724a0f3e51
O problema era que eu tinha que usar
Cells(1)
, mas o mais importante, vai ANTES.Group
.