Invalid Procedure Call or Argument in Pivot Table Macro

  • Hello All,


    The code below is attempting to create a pivot table based on a table of data in a different sheet. The error is "Run-time error '5' Invalid Procedure Call or Argument." Please see the code below. I can post additional lines as well, but the failure occurs on the "set pt = ..." command. Thank you in advance.


    Code
    Dim ws As Worksheet, pt As PivotTable, pc As PivotCaches, pRange As Range
        Set ws = Worksheets("Pivot Table")
        Set pRange = Worksheets("Input").Range("A1").CurrentRegion
        
        Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pRange, _
            Version:=xlPivotTableVersion15).createPivotTable(TableDestination:=ws.Range("A1"), _
            TableName:="pt", DefaultVersion:=xlPivotTableVersion15)
  • Re: Invalid Procedure Call or Argument in Pivot Table Macro


    Hi There,


    I believe this should work for you but is untested.


    Code
    Sub pivoting() '[Sypher7]
    Dim ws As Worksheet, pt As PivotTable, pc As PivotCaches, pRange As Range
    Set ws = Worksheets("Pivot Table")
    Set pRange = Worksheets("Input").Range("A1").CurrentRegion
     
    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pRange)
    Set pt = ws.PivotTables.Add(PivotCache:=pc, TableDestination:=ws.Range("A1"))
    End Sub


    you were declaring pc as a pivotcache but then trying to use pt as the pivotcache

  • Re: Invalid Procedure Call or Argument in Pivot Table Macro


    Thank you very much for the reply. I tried your suggestion and got a "type mismatch" error on the line that creates the pivotcache. What format is expected for SourceData? I thought it was supposed to take a type Range. Is it supposed to be some kind of string representation of the range address?

  • Re: Invalid Procedure Call or Argument in Pivot Table Macro


    would you believe it that it was the most simple of errors.


    your variables you have pc as PivotCaches change it to PivotCache (no s). There is only one.


    I have tested it out, works fine.


    Dan

  • Re: Invalid Procedure Call or Argument in Pivot Table Macro


    Wow, isn't always something that simple. That solved it. Thanks again for all the help. Is there a way to mark the thread solved or whatever terminology is used for this board?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!