VBA Type Mismatch Error on Pivot Table Creation

[nirvehex]

Hi,

I have a code that was working shown below. For some reason I'm pasting in new data into my workorders tab and the data goes from row 2 to row 900. When I got to run this macro now it says "Type mismatch" error. and Highlights the following line within the code:

Code:


Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)




Any ideas? Thanks!

Full Code:


Sub MakePivotTable()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("WorkOrders")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Pivot")
Dim PTCache As PivotCache
Dim PRange As Range
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(2, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(2, 1).Resize(finalRow - 1, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SSPivot")
' 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
With pt.PivotFields("Material")
.Orientation = xlRowField
.Position = 1
.LayoutSubtotalLocation = xlAtTop
.LayoutCompactRow = True
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
.LayoutForm = _
xlOutline
End With
With pt.PivotFields("Equipment")
.Orientation = xlRowField
.Position = 2
.LayoutSubtotalLocation = xlAtTop
.LayoutCompactRow = True
.LayoutForm = _
xlOutline
End With
' Set up the data fields
With pt.PivotFields("Service On")
.Orientation = xlDataField
.Function = xlMax
.Position = 1
End With
With pt.PivotFields("Service On")
.Orientation = xlDataField
.Function = xlMin
.Position = 2
End With
With pt.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlCount
.Position = 3
End With
With pt.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlSum
.Position = 4
End With
With pt.PivotFields("Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 5
.NumberFormat = "$#,##0.00"
End With
With pt.PivotFields("Data")
.Orientation = xlColumnField
End With
With pt.PivotFields("Measurement")
.Orientation = xlPageField
.Position = 1
End With
pt.PivotFields("Measurement").CurrentPage = _
"Tons"
' Now calc the pivot table
With pt
.InGridDropZones = False
.ShowValuesRow = False
End With
With pt
.TableStyle2 = "PivotStyleLight16"
End With
pt.ManualUpdate = False


End Sub

[Tinbendr]

Any ideas?Only that it is expecting a range.

Set a breakpoint at Set PTCashe and run the code. Then, in the Immediate Window, type PRange.Select. If it errors, then investigate the values for row and column. If it doesn't error, go back to the sheet and see what is actually selected.

[nirvehex]

Ok,

I understand what you are saying. Basically get the code to select what it would normally select, but stop it before it errors. How do I set that breakpoint in code?

Thanks.

[Tinbendr]

Put the cursor on the line and press F9. Also available through the debug menus.

[nirvehex]

Ok. I figured that part out. It is selecting the correct range. It just doesn't seem to like something about
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

It's something with PRange or PTCache I believe. Just not sure what.. :confused4

[Tinbendr]

Sorry, I really don't know that much about pivot tables, but if you'll post a sample file, I'll try to help.