VBA pivot table error 5

[Xavier]

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]

It doesn't fail for me.

[snb]

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]

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]

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]

You can't use THisWorkbook, that is the book with the code in. Use ActiveWorkbook, it should be active after opening.

[snb]

Overlooked ?

http://www.vbaexpress.com/forum/show...l=1#post397528

[Xavier]

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.