Jagdev Singh
Active Member
Hi Experts
I am trying to create a pivot table with the following code. I already created one privot table successfully with the below code, but now I am trying to create another one it throws an error of "Error 13 - type mismatch in pivottable". Could you please lemme know what I am missing in the below code or how to fix this error.
Here :
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sub createPivotTable1()
Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField
'Dim pvtFld1 As PivotField
'Worksheet which contains the source data
Set wsData = Worksheets("Data")
'Worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets("Pivot")
'delete all existing Pivot Tables in the worksheet
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
'A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.
'set source data range:
Set rngData = wsData.Range("A1:AB1000")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")
PvtTbl.ManualUpdate = True
Set pvtFld = PvtTbl.PivotFields("Broker Ref 1 (Policy Ref)")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("Assigned")
pvtFld.Orientation = xlRowField
pvtFld.Position = 1
PvtTbl.ManualUpdate = False
End Sub
I am trying to create a pivot table with the following code. I already created one privot table successfully with the below code, but now I am trying to create another one it throws an error of "Error 13 - type mismatch in pivottable". Could you please lemme know what I am missing in the below code or how to fix this error.
Here :
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sub createPivotTable1()
Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField
'Dim pvtFld1 As PivotField
'Worksheet which contains the source data
Set wsData = Worksheets("Data")
'Worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets("Pivot")
'delete all existing Pivot Tables in the worksheet
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
'A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.
'set source data range:
Set rngData = wsData.Range("A1:AB1000")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")
PvtTbl.ManualUpdate = True
Set pvtFld = PvtTbl.PivotFields("Broker Ref 1 (Policy Ref)")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("Assigned")
pvtFld.Orientation = xlRowField
pvtFld.Position = 1
PvtTbl.ManualUpdate = False
End Sub