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

[VJVitale]

I've tried all the solutions recommended in other posts, but still can't get this pivot table to run in my macro. Its a simple pivot table. It comes from a worksheet where I want to be able to dump data in the future to create weekly headcount reports. Here is the highlighted coded when I click on the debugger. Any suggestions?

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Headcount!R1C1:R1048576C12", Version:=6).CreatePivotTable TableDestination _
:="Sheet11!R3C1", TableName:="PivotTable9", DefaultVersion:=6

 

[DanteAmor]

Hi and welcome to MrExel

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Headcount!R1C1:R1048576C12", Version:=6).CreatePivotTable TableDestination _
:="Sheet11!R3C1", TableName:="PivotTable9", DefaultVersion:=6

It is a problem in the version, you must put one of the following, according to your version of excel.

Name	Value	Description
xlPivotTableVersion2000	0	Excel 2000
xlPivotTableVersion10	1	Excel 2002
xlPivotTableVersion11	2	Excel 2003
xlPivotTableVersion12	3	Excel 2007
xlPivotTableVersion14	4	Excel 2010
xlPivotTableVersion15	5	Excel 2013

Source: XlPivotTableVersionList enumeration (Excel)

 

[VJVitale]

Hi and welcome to MrExel

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Headcount!R1C1:R1048576C12", Version:=6).CreatePivotTable TableDestination _
:="Sheet11!R3C1", TableName:="PivotTable9", DefaultVersion:=6

It is a problem in the version, you must put one of the following, according to your version of excel.

Name	Value	Description
xlPivotTableVersion2000	0	Excel 2000
xlPivotTableVersion10	1	Excel 2002
xlPivotTableVersion11	2	Excel 2003
xlPivotTableVersion12	3	Excel 2007
xlPivotTableVersion14	4	Excel 2010
xlPivotTableVersion15	5	Excel 2013

Source: XlPivotTableVersionList enumeration (Excel)

Thank you so much for your response. I actually fixed the problem by creating a new tab, naming the tab and then when creating my pivot table indicated that the table should be created on the new tab. That solved the problem.