☰

组日期数据透视表 VBA-没有发生任何事情，也没有错误

tag-icon • tag-icon microsoft-excel vba pivot-table
组日期数据透视表 VBA-没有发生任何事情，也没有错误

我正在尝试按按月/年分组的日期对数据透视表进行分组。我在网上找到了一些看似很简单的代码，当我测试它时，我没有收到任何错误，但工作簿/数据透视表中没有任何反应。有人能看出哪里出了问题吗？

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

所以我关注的是这个-->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)

但我在另一个网站上看到了这个--> 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)

问题是我必须使用Cells(1)，但更重要的是，它在之前.Group。

相关内容