Creating a Pivot Table via Macro - Errors on Recorded Code

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

 

[JG]

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

Thanks again!

 

[Javed]

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

Thanks again!

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

 

[JG]

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