Getting a mismatch error when creating PivotTable through VBA

[TheRedCardinal]

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:

'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?

 

[JonXL]

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.

 

[RoryA]

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.

 

[TheRedCardinal]

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).