Xavier
12-15-2019, 03:20 AM
Hi, i am trying to write a vba code for creating pivot tables. However, i always get the error "Invalid procedure call or argument" and the code stop working. This error occurs on the line where i try to add a pivot table. The weird thing about this error is that it is not consistent and sometimes i am able to run my code but most of the time i get this error.
The code below is where code has error.
Set pt = ws.PivotTables.Add( _
PivotCache:=pc, _
Tabledestination:=ActiveCell, _
TableName:="PIVOTO")
Below is my entire code for creating a pivot table the line in bold is where i have error in running the code. Any help is greatly appreciated!!
Sub PivotTableww()
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
' set the Pivot-Cache Source Range with the values found for LastRow and LastCol
Set PRange = Range("A1", Cells(LastRow, LastCol))
If ThisWorkbook.PivotCaches.Count = 0 Then
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=PRange, _
Version:=xlPivotTableVersion15)
Else
Set pc = ThisWorkbook.PivotCaches(1)
End If
Set ws = Worksheets.Add
Range("A3").Select
Set pt = ws.PivotTables.Add( _
PivotCache:=pc, _
Tabledestination:=ActiveCell, _
TableName:="PIVOTO")
Set Pf = pt.PivotFields("Driver Name")
Pf.Orientation = xlRowField
Set Pf = pt.PivotFields("Over Speeding")
Pf.Orientation = xlDataField
End Sub
The code below is where code has error.
Set pt = ws.PivotTables.Add( _
PivotCache:=pc, _
Tabledestination:=ActiveCell, _
TableName:="PIVOTO")
Below is my entire code for creating a pivot table the line in bold is where i have error in running the code. Any help is greatly appreciated!!
Sub PivotTableww()
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
' set the Pivot-Cache Source Range with the values found for LastRow and LastCol
Set PRange = Range("A1", Cells(LastRow, LastCol))
If ThisWorkbook.PivotCaches.Count = 0 Then
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=PRange, _
Version:=xlPivotTableVersion15)
Else
Set pc = ThisWorkbook.PivotCaches(1)
End If
Set ws = Worksheets.Add
Range("A3").Select
Set pt = ws.PivotTables.Add( _
PivotCache:=pc, _
Tabledestination:=ActiveCell, _
TableName:="PIVOTO")
Set Pf = pt.PivotFields("Driver Name")
Pf.Orientation = xlRowField
Set Pf = pt.PivotFields("Over Speeding")
Pf.Orientation = xlDataField
End Sub