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

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

--
Message posted via http://www.officekb.com

macro statement will not run a second time without closing andreopening
 

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" wrote:

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

--
Message posted via http://www.officekb.com

--

Dave Peterson

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

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

--
Message posted via http://www.officekb.com