Prev: Customizing Shortcut Menus
Next: Save not triggering Before_Save event
From: JG on 2 Jun 2010 16:20
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.
From: JG on 2 Jun 2010 16:31
Forgot to mention... I'm in Excel 2007.

Thanks again!
From: Javed on 3 Jun 2010 02:39
On Jun 3, 1:31 am, JG <J...(a)discussions.microsoft.com> wrote:
> 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
From: JG on 3 Jun 2010 10:58
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).

"JG" wrote:

> 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.
 | 
Pages: 1
Prev: Customizing Shortcut Menus
Next: Save not triggering Before_Save event