vba - 创建数据透视表的 VBA 代码适用于 Excel 365 [Mac]，但不适用于 Excel Professional Pro Suite [PC]

Question

我在尝试让我的代码在 Excel Professional Pro for PC 上运行时遇到了困难。我已经更改了很多关于 365 和 Professional Pro 之间代码兼容性的设置，并且了解到 365 会自动更新而 Professional Pro 不会。另外，最奇怪的是我开发了一个类似于下面的代码并且它可以工作。我已将工作簿保存为 Excel Enabled-Macro Workbook 以及 Excel Enabled-Macro Template。我不知道该怎么做。请帮忙。该代码用作按钮 [不是 ActiveX 按钮]。

Sub Button1_Click()

'Declare Variables
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


'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("US MASTER").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "US MASTER"
Application.DisplayAlerts = True
Set PSheet = Worksheets("US MASTER")
Set DSheet = Worksheets("US Master Macro")

'Define Data Range
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)

'
'Pivot Table 1
'

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion14). _
CreatePivotTable(TableDestination:=PSheet.Range("B3"), _
TableName:="Total Backlog", DefaultVersion:=xlPivotTableVersion14)

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Range("B3"), TableName:="Total Backlog")

'Add PR ID to Values Field
With PSheet.PivotTables("Total Backlog").PivotFields("PR ID")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With

'Add Classified Cases in Ranges to Row Field
PSheet.PivotTables("Total Backlog").AddFields _
RowFields:=Array("Classified Cases in Ranges")

'Sort Pivot Table
With PSheet
.Range("C4:C100").Sort Key1:=.Range("C4"), Order1:=xlDescending, Header:=xlNo
End With

'Add Total Backlog
PSheet.Range("B2").Value = "Total Backlog"
PSheet.Range("B3").Value = "Days"
'Color
PSheet.Range("B2").Interior.ColorIndex = 4
'Merge
PSheet.Range("B2:C2").Merge

'Change the Color
PSheet.Range("B3").Interior.ColorIndex = 4
PSheet.Range("C3").Interior.ColorIndex = 4


End Sub

标签： vbaexceloffice365pivot-table