Pivot table run-time error 5

Question

Hi,

I think I get crazy over creating Pivot tables with VBA. This is my unicorn (as they say in the movie "Gone in 60 seconds".

I have tried to find the answer in other posts but I cannot get it.

I get Run-time error '5': Invalid procedure call or argument.

I get the error code when I am here in the code

'Create Pivot table from Pivot Cache

Set pvt = pvtCache.CreatePivotTable( _

tabledestination:=StartPvt, \_ 

TableName:="PivotTable3")

This is my code:

Dim sht As Worksheet

Dim pvtCache As PivotCache

Dim pvt As PivotTable

Dim StartPvt As String

Dim SrcData As String

' Make Pivot 

'Determine the data range you want to pivot

SrcData = ActiveSheet.Name & "!" & Range(Cells(VPivotDataRow, VPivotDataRow), Cells(VLastRow, VLastCol)).Address(ReferenceStyle:=xlR1C1)

Debug.Print "SrcData" & SrcData

Debug.Print "VPivotSheet" & VPivotSheet

'Create a new worksheet

Set sht = Sheets.Add

ActiveSheet.Name = VPivotSheet 

'Where do you want Pivot Table to start?

StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

Debug.Print "StartPvt" & StartPvt

'Create Pivot Cache from Source Data

Set pvtCache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, \_ 

SourceData:=SrcData) 

'Create Pivot table from Pivot Cache

Set pvt = pvtCache.CreatePivotTable( _

tabledestination:=StartPvt, \_ 

TableName:="PivotTable3")

From the Debug.print I have the following result.

SrcData: 1RS!R1C1:R1084C24

VPivotSheet: Purchase part

StartPvt: Purchase part!R3C1

Just to be sure I have put in the row

Range(Cells(VPivotDataRow, VPivotDataRow), Cells(VLastRow, VLastCol)).Select

Just above SrcData = ...

And on that selection opened a pivot table manually in excel.

Answer 1

Thanks Mia,

I have gone through all those posts. But I cannot find it.

But I have now narrowed down the issue to the exact question.

The following code is working.

Why does it stop working when for VPivotSheet replace _ with Space

VPivotSheet = "Pivot_1"

to

VPivotSheet = "Pivot 1"

//S

Answer 2

Hi Stefan,

Thank you for querying in our forum.

From your description, it seems that there shows some errors when you try to create Pivot tables with VBA codes.

If so, according to our search, here are some related articles about this error message for your references.

Invalid procedure call or argument (Error 5) | Microsoft Docs

"Run-time error 5" when you use Mid(), Left(), or Right() function (microsoft.com)

Access VBA invalid procedure call or argument - Stack Overflow

vba - Run Time Error 5 - Invalid Procedure Call or Argument - Stack Overflow

excel - Invalid procedure call or argument 5 with VBA Right function - Stack Overflow

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

If above references still failed to resolve the issue on your side, to make sure you get professional help, it is suggested to post a new thread in Microsoft Q & A forum - Visual Basic for Applications (VBA) as engineers in those forum are very proficient in the knowledge of these codes. And they will also focus on your specific codes or situation to provide specific suggestions for you.

Really sorry that engineers in here have limited experience and support resources on VBA related concerns. Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Best Regards,

Mia