Consulting

Results 1 to 8 of 8

Thread: VBA pivot table error 5

  1. #1
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    3
    Location

    VBA pivot table error 5

    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
    Last edited by xld; 12-15-2019 at 04:55 AM. Reason: Added code tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    It doesn't fail for me.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    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

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,883
    Location
    Quote Originally Posted by Xavier View Post
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    3
    Location
    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!!

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    You can't use THisWorkbook, that is the book with the code in. Use ActiveWorkbook, it should be active after opening.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7

  8. #8
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    3
    Location
    Quote Originally Posted by xld View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •