CODEWITHSUNDEEP
vbaexcelexcel-2010pivot-table
ARich
ARich

Reputation: 3279

VBA ChangePivotCache Run-time Error 13: Type Mismatch

The Issue:

You are my last hope. I've been working on this issue for three days now with no luck (and frustratingly little information on the Internet...)

I have a macro that makes a copy of the 'master' file (copies a few modules and a few sheets into the new copy). One of the copied sheets contains several pivots and slicers which reference the 'master' file's data.

This copy of the master file doesn't contain the entire dataset like the master file does and these cuts will be used by different users, so I don't want the pivots to reference the master dataset. So my challenge has been to change the source data for each pivot (preferably all on the same PivotCache) and then connect the applicable slicers to the necessary pivots.

My code to disconnect the slicers from the pivots works fine, and my code to reconnect the slicers works fine. I just can't get the first pivot to change data sources.

The frustrating thing is that on two separate occaisions I've made it work, only to have it throw the Run-time error '13': Type mismatch the next time I test it.

My first thought was that I hadn't been specific enough with workbook/sheet names, so I modified my code to reflect the exact workbook and sheet. Alas, the error still exists. The referenced workbook and sheet both exist, there are no missing column headers, there is a pivot with the name PivotTable1, and my range is being set correctly.

The error is thrown on the second line of the With statement.

My Code (Excerpt):

Dim pTable As PivotTable
Dim LEADData As Range
Dim sCache As SlicerCache
Dim pCache As PivotCache

thislastrow = Sheets("LEAD Data").Range("B" & Rows.Count).End(xlUp).Row
Set LEADData = Workbooks(cutwkbk2).Sheets("LEAD Data").Range("B9:AT" & thislastrow)
'Workbooks(cutwkbk2).Activate

With Workbooks(cutwkbk2).Sheets("Cabinet Reporting")
     'Debug.Print .PivotTables("PivotTable1").SourceData
     .PivotTables("PivotTable1").ChangePivotCache Workbooks(cutwkbk2).PivotCaches.Create(xlDatabase, LEADData)
End With

I've also tried using a pivot index like .PivotTables(1), but that also throws the error. The baffling part to me is how it's worked on two separate occaisions, but then fails even though nothing has changed between tests...

Please offer suggestions! I'm at a loss with this!

Upvotes: 0

Views: 3344

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

A shot in the dark.. Try this (UNTESTED)

Sub Sample()
    Dim pTable As PivotTable
    Dim sCache As SlicerCache
    Dim pCache As PivotCache

    Dim LEADData As String

    thislastrow = Sheets("LEAD Data").Range("B" & Rows.Count).End(xlUp).Row
    LEADData = "LEAD Data!" & "R9C2:R" & thislastrow & "C46"

    With Workbooks(cutwkbk2).Sheets("Cabinet Reporting")
         .PivotTables("PivotTable1").ChangePivotCache Workbooks(cutwkbk2).PivotCaches.Create(xlDatabase, LEADData)
    End With
End Sub

On Second thoughts, something like this

Sub Sample()
    Dim pTable As PivotTable
    Dim sCache As SlicerCache
    Dim pCache As PivotCache

    Dim LEADData As String

    thislastrow = Sheets("LEAD Data").Range("B" & Rows.Count).End(xlUp).Row

    LEADData = ThisWorkbook.Path & "[" & _
               ThisWorkbook.Name & _
               "]LEAD Data!" & _
               "R9C2:R" & thislastrow & "C46"

    With Workbooks(cutwkbk2).Sheets("Cabinet Reporting")
         .PivotTables("PivotTable1").ChangePivotCache Workbooks(cutwkbk2).PivotCaches.Create(xlDatabase, LEADData)
    End With
End Sub

Upvotes: 1

Related Questions