VBA Pivot Table Error 5 - 0_o

[corquando]

Hello, MrExcel World.

    Dim PRange As Range
    Dim lastRow As Long
    Dim finalCol As Long
 
 
 
            lastRow = ActiveSheet.Cells(Application.Rows.Count, 1).End(xlUp).Row
            finalCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
 
            Set PRange = Sheets("Final Rollup").Cells(1, 1).Resize(lastRow, finalCol)
 
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
               PRange, Version:=xlPivotTableVersion12).CreatePivotTable _
               TableDestination:="Final Rollup!R2C5", TableName:="CoreCounts", _
               DefaultVersion:=xlPivotTableVersion12

It stops at the red part and I get a "Run-time error '5': Invalid procedure call or argument."

Oddly, the code came from the macro recorder itself and threw the error even before I transplanted PRange. Everything else is unchanged.

I'm pretty sure I have all the references necessary . . . I am clueless.

Thanks for any help.

 

[Jerry Sullivan]

Hello corquando,

One possible cause is that you are already using that PivotTable name in the same worksheet.

Excel typically creates new PivotTables using an unused name ie. PivotTable1, PivotTable2, etc.

If you record a macro while creating PivotTable2, then try to run that macro immediately after,
you'll get the error you describe because PivotTable2 already exists.

 

[corquando]

Thanks, Jerry.

I'll review the code and process to see if this is the case. I usually try to stay aware of redundancies like that, but it's the ones buried in the heiroglyphics that get by me when they do.

Happy coding.

 

[Jerry Sullivan]

You're welcome. Just to clarify, the problem wouldn't be buried in other parts of your VBA code.
It would be if you run this macro a second time without deleting the previously created PivotTable with the same name: "CoreCounts"

 

[Andrew Poulsom]

If the worksheet's name includes spaces you need to surround it with single quotes:

            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
               PRange, Version:=xlPivotTableVersion12).CreatePivotTable _
               TableDestination:="'Final Rollup'!R2C5", TableName:="CoreCounts", _
               DefaultVersion:=xlPivotTableVersion12

 

[Jerry Sullivan]

If the worksheet's name includes spaces you need to surround it with single quotes:

            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
               PRange, Version:=xlPivotTableVersion12).CreatePivotTable _
               TableDestination:="'Final Rollup'!R2C5", TableName:="CoreCounts", _
               DefaultVersion:=xlPivotTableVersion12

Ahh- good catch!

 

[corquando]

@ Jerry - thanks, gotcha. I'll store that under "Dumb little things that mess up the code."

@Dr. Poulsom - *facepalm* it is the things like this that keep me humble and meek. I knew that - I write more VLOOKUP commands in R1C1 than the next 47 guys. Thanks for the reminder.

It runs beautifully now. Thanks again to both you guys.

Best. Site. Ever.