excel - 使用 VBA 设置空白数据透视表时出错

Question

我想在“数字”列中创建一个具有不同计数的数据透视表。我的数据的第一行如下所示：

所以我想要一个看起来像这样的数据透视表：

数字	数数
3	2
4	3
7	2

现在我已经编写了一个代码来创建一个，但是在插入空白数据透视表时出现错误。我正在尝试制作这个数据透视表几个小时，所以我将非常感谢任何帮助。

我的代码（我知道我没有设置值，但我知道我正在尝试找出空白数据透视表的错误）：

Sub Zadanie3()
'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("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
On Error GoTo 0

Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sheet1")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, "A").End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, "A").Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Number")
.Orientation = xlRowField
.Position = 1
End With



End Sub

标签： excelvbapivot-table

Answer 1

运行您的代码在我端生成一个 Pivot 没有问题，但不一定是您想要的方式（没有“数字”计数的值字段），如您所描述的。所以我建议仔细检查你的文件是否设置正确，重新启动excel，或者创建另一个文件作为测试。

要生成所需的表，其中行字段中包含“数字”，值字段中包含“数字”计数，请在行字段之前添加值字段：

Sub Zadanie3_with_Count()
'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("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
On Error GoTo 0

Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sheet1")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, "A").End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, "A").Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

ActiveSheet.PivotTables("SalesPivotTable").AddDataField ActiveSheet.PivotTables _
        ("SalesPivotTable").PivotFields("Number"), "Count", xlCount

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Number")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("SalesPivotTable").CompactLayoutRowHeader = "Number"

End Sub