Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing and reopening the same file | Excel Discussion (Misc queries) | |||
real estate closing statement | Excel Discussion (Misc queries) | |||
Can I stop Excel from reopening personal.xl when I run a macro? | Excel Discussion (Misc queries) | |||
closing 1 file at a time | Excel Discussion (Misc queries) | |||
My comments reformat after closing and reopening. | Excel Discussion (Misc queries) |