Getting a mismatch error when creating PivotTable through VBA

TheRedCardinal

Active Member
Joined
Jul 11, 2019
Messages
252
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hi all,

I am just playing around with creating some pivot tables from VBA.
I followed an online guide which worked perfectly when following his model, but has not worked when I tried to modify for my own purposes.

Code is here:

Code:
'Declare VariablesDim 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


'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
'On Error Resume Next
'Application.DisplayAlerts = False
'Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Final Data")




'Define Data Range
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)


MsgBox LastRow
MsgBox LastCol


'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")


'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Commodity")
        .Orientation = xlRowField
        .Position = 1
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Country")
        .Orientation = xlRowField
        .Position = 2
End With




'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "#,##0"
        .Name = "Revenue "
End With
'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"
End Sub

I get a mismatch error when I run it.
I have entered the message boxes to makes sure the variables work well and they do.

The error comes at the section labelled 'Define Pivot Cache but actually the code runs, and creates a blank pivot table in the right place, which doesn't seem to make sense to me if the error is at this point.

So I have done something silly, but can anybody spot it?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I don't know anything about creating PivotTables through VBA, but your error would suggest that PCache is the wrong type of variable for assigning what you're trying to assign to it.

One thing to possibly try is not typing PCache when you declare it (remove 'As PivotCache' from the 'Dim PCache...' line). If it works like this, then you can add 'Debug.Print TypeName(PCache)' after the 'Set PCache...' statement to see what type the variable should be.
 
Upvote 0
First off, you are trying to create two pivot tables in the same location, which won't work. (you have two createpivottable lines)

Second, how much data do you have? You're usually better off using a range address (in R1C1 format) for the source data, rather than a Range object.
 
Upvote 0
Thanks both.

Jon - I'll give that a go.

Rory - that is the model of the walkthrough I followed and works perfectly on this example spreadsheet that came with the model:

https://drive.google.com/open?id=1HhXOHZyOJhssahpSmGf-m8tqjIUN-7-6

When I adapt it (all I think I've done is change the headings of the relevant data) I get that error:

https://drive.google.com/open?id=1nOc8jRg0d8wJ5Y15__XlCctwjxT_h82Z

The size of the data fields will vary each period so that is why I went for a dynamic range rather than specific addresses (both the columns and rows will vary).
 
Upvote 0

Forum statistics

Threads
1,224,869
Messages
6,181,484
Members
453,046
Latest member
Excelvbaexpert

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