Type Mismatch Error when trying to create Pivot Table VBA

[mandy123]

Hello everyone. I currently run excel 2019 on my own computer, and excel 2016 at work. This version works on my Excel 2019. When I run this code at work (2016 Excel), I get a "Type Mismatch Error" with this part of the code: "Set PTable = ThisWorkbook.Worksheets("Pivot Table").PivotTables"

I cannot figure it out, and would appreciate how to fix the code to be able to create a pivot table. With my many attempts, I cannot get past the type Mismatch error.

Thank you.

Sub OpenLatestFile()

Dim MyPath As String

Dim Myfile As String

Dim LatestFile As String

Dim LatestDate As Date

Dim LMD As Date

MyPath = ("\\D05MS-70411S2\Public\NavArch\Polar Icebreaker\Design Reviews\Compliance Comments")

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

'MyPath = ("C:\Users\merri\Documents")

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
Myfile = Dir(MyPath & "*.xlsx", vbNormal)

Application.ScreenUpdating = False

If Len(Myfile) = 0 Then
    
    MsgBox "No files were found...", vbExclamation
    
    Exit Sub

End If

Do While Len(Myfile) > 0

    LMD = FileDateTime(MyPath & Myfile)

    If LMD > LatestDate Then

        LatestFile = Myfile

        LatestDate = LMD

    End If

    Myfile = Dir

Loop


Workbooks.Open MyPath & LatestFile

'Delete empty columns
Dim C As Integer
C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
Do Until C = 0
If WorksheetFunction.CountA(Columns(C)) = 0 Then
Columns(C).Delete
End If
C = C - 1
Loop



'Declare Variables
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

'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot Table").Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Pivot Table"
Application.DisplayAlerts = True
Set PSheet = ThisWorkbook.Sheets("Pivot Table")
Set DSheet = ActiveWorkbook.Sheets("Sheet1")
On Error GoTo 0

Application.GoTo Reference:="Table1"
Worksheets("Pivot Table").Activate
On Error Resume Next


'Define Data Range
LastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(3, DSheet.Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Range("A3").Resize(LastRow, LastCol)

'Define Pivot Cache
PRange.Worksheet.Activate

Set PCache = ActiveWorkbook.PivotCaches.Create(1, PRange.Address, 6)

'Create Pivot Table
Dim pvtablecounter As Integer
Set PTable = ThisWorkbook.Worksheets("Pivot Table").PivotTables

For Each PTable In PSheet.PivotTables
    pvtablecounter = pvtablecounter + 1
    Next PTable

On Error GoTo 0
    If PTable Is Nothing Then
     Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(3, 1), TableName:="Table1")
    

Else
PTable.ChangePivotCache PTable.PCache

PTable.ClearAllFilters
PTable.RefreshTable

'Insert data Fields
With PTable
.PivotFields ("Sum of submRequiredFileCount")
.Orientation = xlPageField
.Position = 1
End With

'Insert Row Fields
With PTable.PivotFields("cdrlNumber")
.Orientation = xlRowField
.Position = 1
End With

'Insert Row Fields
With PTable.PivotFields("commentTypeName")
.Orientation = xlRowField
.Position = 2
End With

'Insert Row Fields
With PTable.PivotFields("commentTypeName")
.Orientation = xlColumnField
.Position = 1
End With

PTable.ShowTableStyleRowStripes = True

With PTable.TableStyle2 = "PivotStyleMedium14"
End With

PTable.RowAxisLayout xlTabularRow
Application.ScreenUpdating = True

End If


End Sub

 

[rondeondo]

Hi Mandy
From the looks of things the data type pivottable is not being recognised. One handy VBA feature is the automatic capitalisation of the reserved words. When that doesn't happen it's cause for further investigation. At a guess that data type may be newer than Excel 2016. Try to record a macro for creating a pivot table in 2016 and take a look at the code.

 

[mandy123]

Good morning,

Thank you for your reply. Attached is my recorded Macro. The "Summary Data" Is the "latest file I am pulling from. When I update the code and make it a dynamic range, I get a run time error 438- "Object doesn't support the property or method" and the "Set PTable = PCache = …" is highlighted. Any Help would be appreciated.

 Macro1 Macro
'

'
    Windows("Summary Data.xlsx").Activate
    Windows("Dashboard data.xlsm").Activate
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "[Summary Data.xlsx]Sheet1!R3C1:R60111C35", Version _
        :=6).CreatePivotTable TableDestination:="Pivot Table!R2C2", TableName:= _
        "PivotTable1", DefaultVersion:=6
    Sheets("Pivot Table").Select
    Cells(2, 2).Select
    Windows("Summary Data .xlsx").Activate
    Windows("Dashboard data.xlsm").Activate

 

[rondeondo]

Hi Mandy

how are you trying to convert the recorded macro to be dynamic? I still think it's an Excel version issue. Maybe try using a string variable to define your source dynamically and insert it into the

sourceData :=

line. Maybe use ws.specialCells(xlLastCell).row and ws.specialCells(xlLastCell).column. It's not as neat as a pivottable datatype but may be your best bet.
You may just need two versions of this code for the different Excel versions.

 

[RoryA]

You declared PTable as a PivotTable (singular) whereas the PivotTables property of a worksheet returns a PivotTables collection, with all the pivot tables on that sheet. That's why you get a Type Mismatch.