数据透视缓存语法错误 [英] Error on Pivot Cache syntax

问题描述

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

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

这篇关于数据透视缓存语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！