PDA

View Full Version : VBA pivot table error 5



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

Bob Phillips
12-15-2019, 06:02 AM
It doesn't fail for me.

snb
12-15-2019, 07:31 AM
This code suffices:


Sub M_snbl()
With ActiveWorkbook.PivotCaches.Create(1, Sheet1.Cells(1).CurrentRegion)
With .CreatePivotTable(Sheet1.Cells(20, 1), "PivotTable_snb")
.PivotFields("Driver Name").Orientation = 1
.AddDataField .PivotFields("Over Speeding"), "Min", xlMin
End With
End With
End Sub

Paul_Hossler
12-15-2019, 08:31 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.


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!!


I've found that VBA PTs have changed over the versions

You can try to use the macro recorder and create a PT and then model your real code of that

Xavier
12-18-2019, 03:25 AM
Hi, i realised the code actually works if i place the vba code into the excel file's module. However if i try to run the code from another excel file that opens the excel file that i want to perform the pivot table, i get the error "Invalid procedure call or argument". How do i solve this issue? TIA!!

Bob Phillips
12-18-2019, 06:20 AM
You can't use THisWorkbook, that is the book with the code in. Use ActiveWorkbook, it should be active after opening.

snb
12-18-2019, 07:19 AM
Overlooked ?

http://www.vbaexpress.com/forum/showthread.php?66447-VBA-pivot-table-error-5&p=397528&viewfull=1#post397528

Xavier
12-20-2019, 10:26 PM
You can't use THisWorkbook, that is the book with the code in. Use ActiveWorkbook, it should be active after opening.
Thank you so much! I managed to solve the issue...did not there was such a big difference between activeworkbook and thisworkbook.