• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table issue - Error 13 - type mismatch in pivottable

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
 
Hi Experts

I recorded a macro and it works well as per my current requirement..I am aware that the Source data may gonna vary in future..I want a code to set the rawdata range dynamic..I tried few option, but endup receiving following error msg "Run-time error '1004': Method 'Range' of object '_worksheet' failed"

It throws error under following code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R6852C95", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14


Specifically - SourceData:= _ "Data!R1C1:R6852C95"

The below macro is of around 7000 rows..

Full code -

Sub Macro1()

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
'in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R6852C95", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Pivot").Select
Cells(3, 1).Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Organisation name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Created Date")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("UWSettDueDate")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Posting Ref")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Insured")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PrioritySettType")
.Orientation = xlRowField
.Position = 6
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Internal Notes")
.Orientation = xlRowField
.Position = 7
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Ledger Amount GBP"), "Sum of Ledger Amount GBP", _
xlSum
With ActiveSheet.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Organisation name"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Columns("A:A").Select
Selection.ColumnWidth = 30
Range("B9").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Created Date'[All]", _
xlLabelOnly, True
Range("B4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Created Date").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("C4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("UWSettDueDate").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
Range("D4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Posting Ref").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("E4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Insured").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
Range("F4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("PrioritySettType"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("G4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Internal Notes").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
Columns("G:G").Select
Selection.ColumnWidth = 30
ActiveSheet.PivotTables("PivotTable1").PivotSelect _
"'16320M13000005APM' 'Al-Futtaim Willis Co LLC' '12/30/2013 13:33:00' '10/18/2014'" _
, xlDataAndLabel, True
Columns("E:E").ColumnWidth = 20.71
Columns("E:E").ColumnWidth = 26.14
Range("A9").Select
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
ActiveWindow.Zoom = 90
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("H:H").Select
Selection.Style = "Comma"
Range("A4").Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Business Unit")
.Orientation = xlRowField
.Position = 5
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Range("E4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Business Unit").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
Range("E7").Select
Columns("F:F").ColumnWidth = 15.71
Columns("E:E").ColumnWidth = 20.86
Columns("F:F").ColumnWidth = 19.43
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("G5").Select
Columns("G:G").ColumnWidth = 14.43
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Internal Notes'[All]", _
xlLabelOnly, True
Range("D7").Select
Columns("D:D").ColumnWidth = 23.29
Columns("H:H").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
Selection.Style = "Comma"
Range("A6").Select
End Sub
 
Back
Top