VBA:类型与PivotCache不匹配 [英] VBA: Type Mismatch with PivotCaches

问题描述

我正在尝试在文档中插入pivot table.以下代码将引发mismatch错误，但是，我不确定在行中(Set pCache ...行)向何处/为什么将其引发.在引发错误时，仍会创建空白的pivot table

I am trying to insert a pivot table into a document. The following code throws a mismatch error, however, I am not sure where/why it is throwing it within the line (the Set pCache... line. While the error is thrown, the blank pivot table is still created.

我要去哪里错了?

    pivot_lastRow = mainSheet.Cells(mainSheet.Rows.count, "A").End(xlUp).Row 'may need to change "A"
    pivot_lastCol = mainSheet.Cells(1, mainSheet.Columns.count).End(xlToLeft).Column

    Set pivotRange = mainSheet.Range(mainSheet.Cells(1, 1), mainSheet.Cells(pivot_lastRow, pivot_lastCol))

    Set pCache = wbk.PivotCaches.Create(SourceType:=xlDatabase, _
             SourceData:=pivotRange).CreatePivotTable(TableDestination:=pivotSheet.Cells(2, 2), _
             TableName:="Test")

推荐答案

您正在创建数据透视表，并尝试将其放入数据透视表缓存对象中，这是行不通的！ ;)

You are creating a pivot table and try to fit it into a pivot cache object, that can't work! ;)

分隔最后一条语句:

Dim pCache As PivotCache
Dim pTable As PivotTable

 pivot_lastRow = mainSheet.Cells(mainSheet.Rows.count, "A").End(xlUp).Row 'may need to change "A"
    pivot_lastCol = mainSheet.Cells(1, mainSheet.Columns.count).End(xlToLeft).Column

    Set pivotRange = mainSheet.Range(mainSheet.Cells(1, 1), mainSheet.Cells(pivot_lastRow, pivot_lastCol))

    Set pCache = wbk.PivotCaches.Create(SourceType:=xlDatabase, _
             SourceData:=pivotRange)

    Set pTable = pCache.CreatePivotTable(TableDestination:=pivotSheet.Cells(2, 2), _
             TableName:="Test")

这篇关于VBA:类型与PivotCache不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！