Error Mismatch while setting up a Pivot Table from Pivot Cache

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
437
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:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top