如何在Excel VBA中创建数据透视表？

Question

我想创建一个数据透视表，但我得到了我最后一行代码的错误。

Dim WSD As Worksheet 
Dim WSD2 As Worksheet 
Dim PTCache As PivotCache 
Dim PT As PivotTable 
Dim PRange As Range 
Dim FinalRow As Long 
Dim FinalCol As Long 
Set WSD = Worksheets("SKU Sum") 
Set WSD2 = Worksheets("Finelines") 

' Select the data for pivot table 

FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row 
FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column 
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) 
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange) 

Set PT = WSD.PivotTables.Add(PivotCache:=PTCache, TableDestination:=WSD2.Range(A1), TableName:="Pivotab") 

任何帮助将不胜感激。

感谢，

摹

Answer 1

最后一行替换当前的PivtoCache行：

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange) 

有了：

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange.Address(False, False, xlA1, xlExternal)) 

此外，你所得到的最后一行的错误，因为你设置的枢轴表WSD2.Range(A1)的位置，括号内的字符串需要有"。所以也修改为WSD2.Range("A1")

Answer 2

替换

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD2.Range("A1"), TableName:="Pivotab")