VBA创建数据透视表,类型不匹配?我究竟做错了什么? [英] VBA to Create PivotTable, Type Mismatch? What Am I Doing Wrong?
本文介绍了VBA创建数据透视表,类型不匹配?我究竟做错了什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在行tabledestination:=("pivot!A3")
上获取类型不匹配
我想添加一个工作表并将其命名为数据透视表",并在该工作表上创建数据透视表.
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.
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
推荐答案
这对我有用...
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
警告::如果存在PIVOT工作表,则会出现错误.也许您想将其添加到您的代码中?
Caution: If the sheet PIVOT exists you will get an error. Maybe you would like to add this to your code?
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Pivot").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Sheets.Add.Name = "Pivot"
这篇关于VBA创建数据透视表,类型不匹配?我究竟做错了什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文