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

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
1,027
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I'm trying to record a macro that inserts a pivot table from data from one worksheet onto a newly created worksheet, but I'm getting this error here...

"runtime 5"

"Invalid procedure call or argument"


The code in RED is the code below that it bugs on.

Rich (BB code):
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet2").Name = "Cht-P"
    Sheets("DB-P").Select
    Range("A1").Select
    Selection.CurrentRegion.Select
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
       "DB-P!R1C1:R6674C5", Version:=xlPivotTableVersion12).CreatePivotTable _
       TableDestination:="Cht-P!R1C1", TableName:="PivotTable1", _
       DefaultVersion:=xlPivotTableVersion12
    Sheets("Cht-P").Select
    Cells(1, 1).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Cht-P!$A$1:$C$18")
    ActiveWorkbook.ShowPivotChartActiveFields = True
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveWorkbook.ShowPivotChartActiveFields = False

Can anyone help me fix this?

Thanks much!
 
Re: Runtime error 5, Invalid Procedure call or argument error

Why are you subtracting 3?
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Re: Runtime error 5, Invalid Procedure call or argument error

I know. :biggrin:
 
Upvote 0
<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>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top