VBA 创建数据透视表，类型不匹配？我究竟做错了什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18835056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to Create PivotTable, Type Mismatch? What Am I Doing Wrong?
提问by ggmkp
Getting Type Mismatch on the line tabledestination:=("pivot!A3")
I want to add a sheet and name it "Pivot" and create PivotTable on that sheet.
在线获取类型不匹配tabledestination:=("pivot!A3")
我想添加一个工作表并将其命名为“数据透视表”并在该工作表上创建数据透视表。
Dim pt As PivotTable
Dim Pcache As PivotCache
Sheets.add.name = "Pivot"
Sheets("DATA").Select
Set Pcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Cells(1, 1).CurrentRegion)
Set pt = ActiveSheet.PivotTables.Add(PivotCache, tabledestination:=("pivot!A3"))
With pt
PivotFields.Subtotals(1) = False
.InGridDropZones = True
.RowAxisLayout xlTabularRow
.PivotFields("Apple").Orientation = xlRowField
.PivotFields("Apple Qty").Orientation = xlDataField
End With
采纳答案by Siddharth Rout
This worked for me...
这对我有用...
Sub Sample()
Dim pt As PivotTable
Sheets.Add.Name = "Pivot"
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Sheets("DATA").Cells(1, 1).CurrentRegion)
Set pt = .CreatePivotTable(TableDestination:="Pivot!R3C1")
End With
'~~> Rest of Code
End Sub
Or if you want to do it your way then
或者如果你想按照自己的方式去做
Sub Sample()
Dim pt As PivotTable
Dim Pcache As PivotCache
Sheets.Add.Name = "Pivot"
Set Pcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, _
Sheets("DATA").Cells(1, 1).CurrentRegion)
Set pt = Pcache.CreatePivotTable(tabledestination:=("Pivot!R3C1"))
'~~> Rest of the code
End Sub
Caution:If the sheet PIVOT exists you will get an error. Maybe you would like to add this to your code?
注意：如果表 PIVOT 存在，您将收到错误消息。也许您想将此添加到您的代码中？
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Pivot").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Sheets.Add.Name = "Pivot"
回答by zedfoxus
Instead of using
而不是使用
Set pt = ActiveSheet.PivotTables.Add(PivotCache, tabledestination:=("pivot!A3"))
I used this to move forward:
我用它来前进：
Sheets("Pivot").Activate
Set pt = ActiveSheet.PivotTables.Add(PivotCache:=Pcache, TableDestination:=Range("A3"))
Notice the addition of Sheets("Pivot").Activateand PivotCache:=Pcache. I haven't checked the validity of code below that statement, though.
注意添加Sheets("Pivot").Activate和PivotCache:=Pcache。不过，我还没有检查该语句下方代码的有效性。
