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

[VBAProIWish]

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.

    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!

 

[RoryA]

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

Why are you subtracting 3?

 

[santhosh2250]

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:".

 

[RoryA]

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]

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?

 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

 

[RoryA]

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?

 

[philip_eng]

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]

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

In that case try:

TableDestination:= _
        "'Summary'!R30C1"

assuming there isn't already a pivot table there.

 

[philip_eng]

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

Thank you! You are a genius!!

 

[RoryA]

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

I know.

 

[Daksh009]

<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>