OGeek|极客世界-中国程序员成长平台

标题: vba - Type mismatch when setting up pivotcache [打印本页]

---

作者: 菜鸟教程小白    时间: 2022-6-1 20:10
标题: vba - Type mismatch when setting up pivotcache

I have pCach as PivotCache

when I just do

ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pTRng).CreatePivotTable(TableDestination:= _
    wOPT.Cells(3, 1), TableName:="PivotTable2")

It works as expected and inserts pivotcache on the destination cell

But when I try this it gives me type mismatch error?

 Set pCach = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pTRng).CreatePivotTable(TableDestination:= _
    wOPT.Cells(3, 1), TableName:="PivotTable2")

---

Best Answer-推荐答案

Try splitting setting the PivotCache and the PivotTable to 2 seperate code lines, like in the code below :

Dim pTbl  As PivotTable
Dim pCach As PivotCache

' set the Pivot Cache
Set pCach = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pTRng)

' create a new Pivot Table in "wOPT" sheet, start from Cell A3
Set pTbl = wOPT.PivotTables.Add(PivotCache:=pCach, TableDestination:=wOPT.Range("A3"), TableName:="PivotTable2")

---

欢迎光临 OGeek|极客世界-中国程序员成长平台 (http://ogeek.cn/)

Powered by Discuz! X3.4