CODEWITHSUNDEEP
vbaexcelpivot-table
Stupid_Intern
Stupid_Intern

Reputation: 3450

Type mismatch error while adding pivotcache

This returns type mismatch error I don't know why.

I asked the same questing yesterday in which I found there were some merged cells hence I removed merge cells today but still it's not working giving the same error

The code works fine with other sheet data but it returns the error for that specific sheet.

I tried this:

Set wD = Sheets.Add
ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=wS.UsedRange).CreatePivotTable _
TableDestination:=wD.Range("A3")

And this

Set wD = Sheets.Add
ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=wS.Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:=wD.Name & "!R3C1"

This is what source data sheet looks like

Source Data Sheet

Upvotes: 0

Views: 207

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

Try the code below:

Sub UpdatePivot()

Dim wD                              As Worksheet
Dim wS                              As Worksheet

Dim PvtTbl                          As PivotTable
Dim PvtCache                        As PivotCache
Dim PvtRangeStr                     As String


Set wS = Sheets("inventory master")
Set wD = Sheets.Add

PvtRangeStr = "'" & wS.Name & "'!" & wS.UsedRange.Address

' for debug
Debug.Print PvtRangeStr

' set Pivot Cache using another reference to the range
Set PvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRangeStr, Version:=xlPivotTableVersion14)    

' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = wD.PivotTables("PivotTable1")

On Error GoTo 0
If PvtTbl Is Nothing Then

    ' create a new Pivot Table in wD Sheet, start from Cell A3
    Set PvtTbl = wD.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=wD.Range("A3"), TableName:="PivotTable1")
Else
    ' just refresh the Pivot cache with the updated data
    PvtTbl.ChangePivotCache PvtCache
    PvtTbl.RefreshTable
End If

End Sub

Upvotes: 1

Related Questions