Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Pivot Table via Macro - Errors on Recorded Code
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:=xlDa tabase, 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Pivot Table via Macro - Errors on Recorded Code
Forgot to mention... I'm in Excel 2007.
Thanks again! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Pivot Table via Macro - Errors on Recorded Code
On Jun 3, 1:31*am, JG 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Pivot Table via Macro - Errors on Recorded Code
I figured out what the problem was and am posting it here to help others in
the futu 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:=xlDa tabase, 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 macro recorded pivot table | Excel Worksheet Functions | |||
macro creating new pivot table | Excel Programming | |||
problem creating a pivot table using a macro | Excel Programming | |||
Creating a macro which presses a button containing a recorded macro | Excel Programming | |||
Help with a macro creating a formula for a pivot table | Excel Programming |