VBA for creating pivot table

Question

I am trying to use VBA to insert a pivot table.

However, every time I try to run it I get an error: "Run-time error 13: Type mismatch" on the Define Pivot Cache portion.

I've tried changing the variable names as well as not using some of them, but I still get the same error. Am I missing something?

Sub PivotTableAdd()

    '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").Delete
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "Pivot"
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("Pivot")
    Set DSheet = Worksheets("Report")

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

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

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

End Sub

Edit: I changed this line and got it to work:

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange.Address(True, True, xlR1C1, True))

Tim Williams · Accepted Answer · 2019-09-05 19:03:24Z

1

Change this:

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PivotTable")

To:

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)

Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
                               TableName:="PivotTable")

In your original code CreatePivotTable returns a PivotTable, not a PivotCache, so giving you the "type mismatch" error

Edit: tested this and works fine for me

Sub PivotTableAdd()

        '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").Delete
        Sheets.Add Before:=ActiveSheet
        ActiveSheet.Name = "Pivot"
        Application.DisplayAlerts = True
        Set PSheet = Worksheets("Pivot")
        Set DSheet = Worksheets("Report")

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

        'Define Pivot Cache
       Set PCache = ActiveWorkbook.PivotCaches.Create( _
                  SourceType:=xlDatabase, SourceData:=PRange)

       Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
                               TableName:="PivotTable")
End Sub

edited Sep 5, 2019 at 19:03

answered Sep 4, 2019 at 19:30

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Will Over a year ago

Tim, I made the change mentioned and I'm still getting the same error. Any ideas?

Will Over a year ago

On the Set PCache line.

Collectives™ on Stack Overflow

VBA for creating pivot table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related