Trying to put a pivot table report on an existing sheet causes err

[Guest]

Thanks to Frank Kabel, I can now make great pivot tables. The problem I am having is that I want to write a macro to generate pivot tables, and I want the pivot table report to be saved on a worksheet called "Sum of Open", which is overwritten each time the macro runs.

I have created the sheet named "Sum of Open" which is in the same workbook, but when i try to save it to there ('Sum of Open!'), I get an error:

"Your formula contains and invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again."

Am I referring to the sheet incorrectly?

Thanks!

 

[Guest]

Include a cell reference with the sheet name. For example to start the pivot table in cell A3:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R4C4").CreatePivotTable _
TableDestination:="'Sum of Open'!R3C1", _
TableName:="PivotTable2"

 

[AlfD]

Hi!

I don't know if you did the same thing in your macro as in your post.
In the latter you refer to 'Sum of Open!'

Try reversing the last 2 characters (' and ! )

Al

 

[Guest]

Thanks for the help! It almost works.

I have tried for several hours to fix the problem, but I can't figure out what is causing it. The error message I get is "Run-time error 1004 -- Pivot table wizard method of worksheet class failed."

When i debug it, it's failing on this line:
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

Here is my code:

Sub CreatePivotFinal()
'
' CreatePivotFinal Macro
' Macro recorded 7/14/2004 by Erikka
'

'
Sheets("Sheet Open").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Open SPRs"
Range("C1").Select
ActiveCell.FormulaR1C1 = "State"
Range("A2").Select

'create the pivot table in the sheet named Sum of Open
Sheets("Sum of Open").Select
Cells.Select
Selection.Clear
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="'Sheet Open'!R1C1:R1000C3").CreatePivotTable _
TableDestination:="'Sum of Open'!R3C1", _
TableName:="PivotTable2"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").ColumnGrand = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Date"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Open SPRs").Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Open SPRs").Function _
= xlSum
Range("D5").Select

End Sub

Any ideas? Thanks for any suggestions.

Erikka

 

[Guest]

I just figured it out--sadly, it was a copy/paste error and once I removed the line where it was breaking, it started working.

 

[Debra Dalgleish]

You shouldn't need that line of code -- just remove it.