Error on Pivot Cache syntax

Question

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

Answer 1

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.

Code

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

---

Edit 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