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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Re: Runtime error 5, Invalid Procedure call or argument error

Why are you subtracting 3?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

santhosh2250

New Member
Joined
Dec 12, 2012
Messages
2
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 a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

philip_eng

New Member
Joined
Jun 8, 2017
Messages
6
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 a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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?
 
Last edited:

philip_eng

New Member
Joined
Jun 8, 2017
Messages
6
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

philip_eng

New Member
Joined
Jun 8, 2017
Messages
6
Re: Runtime error 5, Invalid Procedure call or argument error

Thank you! You are a genius!! :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Re: Runtime error 5, Invalid Procedure call or argument error

I know. :biggrin:
 

Daksh009

New Member
Joined
Jan 17, 2018
Messages
4
<code class="spaces" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); color: rgb(0, 0, 0); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;">i am also getting the same error. pls help
</code><code class="keyword" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-weight: bold !important; direction: ltr !important; display: inline !important; color: rgb(0, 102, 153) !important;">Set</code> <code class="plain" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); color: rgb(0, 0, 0); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;">myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & </code><code class="string" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important; color: blue !important;">"!"</code> <code class="plain" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); color: rgb(0, 0, 0); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;">& mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & </code><code class="string" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important; color: blue !important;">"!"</code> <code class="plain" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); color: rgb(0, 0, 0); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;">& myDestinationRange, TableName:=</code><code class="string" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important; color: blue !important;">"PivotTableExistingSheet</code><code class="plain" style="box-sizing: border-box; font-size: 16px; background-color: rgb(248, 248, 248); color: rgb(0, 0, 0); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background-image: none !important; background-position: initial !important; background-size: initial !important; background-repeat: initial !important; background-attachment: initial !important; background-origin: initial !important; background-clip: initial !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;">
</code>
 

Watch MrExcel Video

Forum statistics

Threads
1,102,665
Messages
5,488,178
Members
407,630
Latest member
Mehezabin

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top