Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

Thread: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,990
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    Why are you subtracting 3?

  2. #12
    New Member
    Join Date
    Dec 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    let me explain the scenario.. Basically this script is about calculating response time. im extracting mails from outlook from a specific folder to an excel where it contains "subject", received time and replied time.
    i can get replied time by the ITEM "Received time" and for original mail received time, am extracting date&time stamp from the body of the replied email between "Sent:" and "To:".

    3 is nothing but the spaces before "To:".
    Last edited by RoryA; Jun 8th, 2017 at 07:50 AM.

  3. #13
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,990
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    My guess would be that it's not finding "To:" or there aren't three characters before it, so you're ending up passing 0 or a negative number to the left function.

  4. #14
    New Member
    Join Date
    Jun 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    Sorry for digging up an old thread. Im having similar issues. I am getting the run-time error '5': Invalid procedure call or argument with the following text.
    I reviewed the table destination for the single quotes and i think i already had that.

    Any other ideas?
    Code:
     ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
            Array(Array("Summary!R10C1:R23C2", "Item1"), Array("Summary!R10C3:R23C4", "Item2") _
            , Array("Summary!R10C5:R23C6", "Item3"), Array("Summary!R10C7:R23C8", "Item4"), _
            Array("Summary!R10C9:R23C10", "Item5")), Version:=xlPivotTableVersion15). _
            CreatePivotTable TableDestination:= _
            "'[Time Sheet Template.xlsm]Summary'!R30C1", TableName:="PivotTable70", _
            DefaultVersion:=xlPivotTableVersion15
    Last edited by RoryA; Jun 8th, 2017 at 07:50 AM.

  5. #15
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,990
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    Welcome to the forum.

    Is the source data sheet in the same workbook as the destination sheet? Which workbook is active when you are running that code?

  6. #16
    New Member
    Join Date
    Jun 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    Yes the source data sheet is in the same workbook.
    The source data is actually on the same sheet as the destination sheet for the graph (called "Summary").
    I am only using 1 workbook when running this code.

  7. #17
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,990
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    In that case try:

    Code:
    TableDestination:= _
            "'Summary'!R30C1"
    assuming there isn't already a pivot table there.

  8. #18
    New Member
    Join Date
    Jun 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    Thank you! You are a genius!!

  9. #19
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,990
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error

    I know.

  10. #20
    New Member
    Join Date
    Jan 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    i am also getting the same error. pls help
    SetmyPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!"& mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!"& myDestinationRange, TableName:="PivotTableExistingSheet

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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