Creating a Pivot Table via Macro - Errors on Recorded Code

  • Thread starter Thread starter JG
  • Start date Start date
J

JG

I have an Excel workbook that is getting generated from another tool (Quality
Center). It has output data on a tab called "FailedBlocked" and another tab
that is empty called "TC by Def".

I have recorded a macro to create the pivot table. The code Excel creates is:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FailedBlocked!R1C1:R1090C13", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="TC by Def!R1C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

Sheets("TC by Def").Select
Cells(1, 1).Select

When I run the macro, I am getting an "Invalid procedure call or argument
(Error 5)" message. Is the code somehow malformed on record? What could be
invalid here (should I remove spaces from my sheet names? Should that
matter?)?

Thanks.
 
Forgot to mention... I'm in Excel 2007.

Thanks again!
Click to expand...

You must have run it more than once.In excel In one cell only 1
pivottable can be pasted and the table name also should be unique.


TableDestination:="TC by Def!R1C1"-----This should change with each
run
TableName:="PivotTable1"-----This should change with each run
 
I figured out what the problem was and am posting it here to help others in
the future:

The problem lies here in the part of the code that reads:
TableDestination:="TC by Def!R1C1"

Now, mind you, this is generated by Excel. Since the sheet name has spaces
in it, Excel should have encapsulated TC by Def with single quotes.

As soon as I change that part of the code to:
TableDestination:="'TC by Def'!R1C1"

It works. Every time.

Javed, thanks for your input, but I was running the code on spreadsheets
that had not yet had the pivot table run (same data set, new file).
 
Back
Top