nirvehex
07-24-2014, 09:25 AM
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
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