macro statement will not run a second time without closing and reopening

[Pete Straman via OfficeKB.com]

I recorded a macro that created two pivot tables from an external DB. The
first time it runs in "Book1" ok. The second time the statement below
causes a run time error. I have to close and re-open excel to make it run
all the way through on another database. What can I do to make it run in
"Book2" etc. without having to close and re-open excel.

'******* Run time error 1004 unable to get the Pivot Tables property of the
worksheet class

ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="dosmoyr", _
ColumnFields:="transmoyr", PageFields:="facilityid"

Pete Straman

 

[Dave Peterson]

It looks like that there is no pivottable called "Pivottable2" on that
activesheet.

If you're creating a single pivottable on each sheet, maybe...

ActiveSheet.PivotTables(1).AddFields RowFields:="dosmoyr", _
ColumnFields:="transmoyr", PageFields:="facilityid"

 

[Pete Straman via OfficeKB.com]

Thanks for the input. It end up causing confusion in table numbers
reference in code further down the line. However, your suggestion made me
try adding the TableName:= "PivotTable2" to the first line of code below
and it worked. There are things I still do not understand that I have
referenced if anyone cares to comment. I am copying one table to make
another on the same sheet.

ActiveSheet.PivotTableWizard TableDestination:="Sheet1!R1C3", TableName:=
"PivotTable2"


ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="dosmoyr", _
ColumnFields:="transmoyr", PageFields:="facilityid"


*** This code works above to change PivotTable1 but not here. It does not
crash. It just does nothing. I tried changing "2" to "PivotTable2' and
still nothing
With ActiveSheet.PivotTables(2).PivotFields("facilityid") _
.CurrentPage = .PivotItems(1).Value
End With

*** I am removing the "Revenue" data and replacing with "Payments"
*** I do not understand why "PivotTable1" is referenced after
*** I have copied and renamed it to "PivotTable2"
ActiveSheet.PivotTables(1).PivotFields("Revenue").Orientation = _
xlHidden
With ActiveSheet.PivotTables(1).PivotFields("Sum of Payments")
.Orientation = xlDataField
.Caption = "Payments"
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With

Pete Straman