Run time error 13 type mismatch - building a pivot table with vba

spq24

Board Regular
Joined
Jan 18, 2010
Messages
52
This is the code I have...This same code works in a different sheet (I copied and pasted it and changed the necessary names) but I get an error in this new workbook...anyone know why?

Sub pt()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Metro-E Tasks")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Metro-E Fiber Ready_Core Ready")
Dim PTCache As PivotCache
Dim PRange As Range

Sheets("Metro-E Fiber Ready_Core Ready").Select

' Find the last row with data
Dim lastRow As Long
lastRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column

' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(lastRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange) <----- THIS IS WHERE THE ERROR OCCURS

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(3, 1), _
TableName:="PTD Change Rootcause")

' Define the layout of the pivot table

' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True

' Set up the row fields
pt.AddFields RowFields:=Array( _
"PTD Change Root cause"), ColumnFields:=Array("Customer Name")

' Set up the data fields
With pt.PivotFields("Customer Name")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With

' Now calc the pivot table
pt.ManualUpdate = False

ActiveSheet.PivotTables("PTD Change Root cause").TableStyle2 = "PivotStyleLight16"


End Sub
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi spq24,

This line is giving you an error...
Code:
ActiveWorkbook.PivotCaches.Add

because there is no PivotCaches.Add Method. Try...

Code:
ActiveWorkbook.PivotCaches.Create

I haven't looked at your entire code to see if there are any other problems.
Let us know if you have any other problems getting this working.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top