数据透视缓存语法错误

本文介绍了数据透视缓存语法错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

当宏到达此行时，运行以下代码时出现类型不匹配"错误:

While running the below code "Type mismatch" error appears when the macro reaches this line:

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="ERA_Dashboard")

如何更正代码上的错误?

How do I correct the error on the code?

Sub Pivot() Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long Application.DisplayAlerts = False Worksheets("Summary").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "Summary" Application.DisplayAlerts = True Set PSheet = Worksheets("Summary") Set DSheet = Worksheets("Content") LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) Sheets("Content").Select Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="ERA_Dashboard") Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="ERA_Dashboard") With ActiveSheet.PivotTables("ERA_Dashboard").PivotFields("Issue Status") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ERA_Dashboard").PivotFields("Issue Status") .Orientation = xlDataField .Position = 1 .Function = xlCount .NumberFormat = "#,##0" .Name = "Issue Status" End With End Sub

推荐答案

无需删除并重新创建摘要"表，只需使用下面的代码即可.下面的代码将使用更新的 PCache 创建(或更新)摘要"工作表中的 PTable 数据透视表.

There is no need to delete and re-create "Summary" sheet, just use the code below. The code below will create (or update) the PTable Pivot-Table in "Summary" worksheet with the updated PCache.

代码

Option Explicit Sub Pivot() Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long Set PSheet = Worksheets("Summary") Set DSheet = Worksheets("Content") With DSheet LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set PRange = .Range("A1").Resize(LastRow, LastCol) ' set data range for Pivot Table End With 'Set the Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, PRange) ' add this line in case the Pivot table doesn't exit >> first time running this Macro On Error Resume Next Set PTable = PSheet.PivotTables("ERA_Dashboard") ' check if "ERA_Dashboard" Pivot Table already created (in past runs of this Macro) On Error GoTo 0 If PTable Is Nothing Then ' create a new Pivot Table in "Summary" sheet Set PTable = PSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=PSheet.Range("A1"), TableName:="ERA_Dashboard") With PTable.PivotFields("Issue Status") .Orientation = xlRowField .Position = 1 End With With PTable.PivotFields("Issue Status") .Orientation = xlDataField .Position = 1 .Function = xlCount .NumberFormat = "#,##0" .Name = "Issue Status" End With Else ' just refresh the Pivot cache with the updated Range PTable.ChangePivotCache PCache PTable.RefreshTable End If End Sub

编辑1 :

Option Explicit Sub Pivot() Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim LastRow As Long Dim LastCol As Long Set PSheet = Worksheets("Summary") Set DSheet = Worksheets("Content") With DSheet LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set PRange = .Range("A1").Resize(LastRow, LastCol) ' set data range for Pivot Table End With ' Set the Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion14) ' add this line in case the Pivot table doesn't exit >> first time running this Macro On Error Resume Next Set PTable = PSheet.PivotTables("ERA_Dashboard") ' check if "ERA_Dashboard" Pivot Table already created (in past runs of this Macro) On Error GoTo 0 If PTable Is Nothing Then ' create a new Pivot Table in "Summary" sheet Set PTable = PSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=PSheet.Range("A1"), TableName:="ERA_Dashboard") Else ' just refresh the Pivot cache with the updated Range PTable.ChangePivotCache PCache PTable.RefreshTable End If End Sub