Error Mismatch while setting up a Pivot Table from Pivot Cache

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
318
I'm attempting to form a pivot table on a separate worksheet from tabular data on worksheet("PivotRaw"). I'm getting an mismatch error which I think is due to the TableDestination parameter. I initially passed a range, but upon some research have switched over to a string argument. The funny thing is that even though I get the mismatch error, it looks like the line on which the mismatch error is assigned has in part executed in terms of the PivotTable Name (i.e. the empty pivot table displays the correct name). See code comments below for what I mean.

Any thoughts?

VBA Code:
Private Sub MakePivotTable()

Dim PSheet As Worksheet
Dim DestRange As String
Dim PCache As PivotCache
Dim PTable As PivotTable

On Error Resume Next
Application.DisplayAlerts = False

'Add PivotTable Sheet (delete old one if present)
If IsError(ThisWorkbook.Worksheets("PivotTable")) Then
Sheets.Add After:=ThisWorkbook.Worksheets("PivotRaw")
ActiveSheet.Name = "PivotTable"
Else
Worksheets("PivotTable").Delete
Sheets.Add After:=ThisWorkbook.Worksheets("PivotRaw")
ActiveSheet.Name = "PivotTable"
End If

Set PSheet = ThisWorkbook.Worksheets("PivotTable")
Application.DisplayAlerts = True
On Error GoTo 0

'Define Table Destination (added this after research - I originally passed through a range object)
DestRange = PSheet.Name & "!" & PSheet.Range("A3").Address(ReferenceStyle:=xlR1C1)

'Code errors on next line.  PivotTable worksheet is correctly added and I can see the emtpy "DamagePivotTable" pivot table on the sheet.
'Empty pivot table also starts at R3C1 as outlined above.  So I don't know why I'm mismatching here.

'Define Pivot Cache ("TotalDamage" is a named table on the PivotRaw worksheet)
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="TotalDamage")

'Define Pivot Table
Set PTable = PCache.CreatePivotTable(TableDestination:=DestRange, TableName:="DamagePivotTable")

'Insert Row Fields
With ActiveSheet.PivotTable("DamagePivotTable").PivotFields("Plate Area")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTable("DamagePivotTable").PivotFields("Damage")
.Orientation = xlRowField
.Position = 2
End With

'Insert Column Field
With ActiveSheet.PivotTable("DamagePivotTable").PivotFields("Press #")
.Orientation = xlColumnField
.Position = 1
End With

'Insert Values (Function) Field
With ActiveSheet.PivotTable("DamagePivotTable").PivotField("Damage")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
.NumberFormat = "#,##0"
.Name = "Damage Count"
End With

End Sub
 
Last edited:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
318
Fixed the issue by the simplest of code fixes - but I still don't understand why the original didn't work.

Old code:
VBA Code:
Dim PCache As PivotCache
Dim PTable As PivotTable

Fix
VBA Code:
Dim PCache As Variant
Dim PTable As Variant
 

Watch MrExcel Video

Forum statistics

Threads
1,114,236
Messages
5,546,664
Members
410,753
Latest member
Ian R
Top