Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with a recorded macro to create two pivot tables from same data
All:
I recorded a macro to create two pivot tables. I create the second table by copying the first, moving over two columns, paste the data, and them go into the layout to change the data and column fields. When I run the macro it gives me a run time error listed below. How do I change the code to create the second table and fields like I did when I record it? Columns("A:B").Select Selection.Copy Range("C1").Select ActiveSheet.Paste Range("C7").Select Application.CutCopyMode = False ActiveSheet.PivotTableWizard TableDestination:="Sheet1!R1C3" ********* Problem starts here ************* Run-time error ‘1004-: Unable to get the PivotTables property of the worksheet class ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="dosmoyr", _ ColumnFields:="transmoyr", PageFields:="facilityid" ActiveSheet.PivotTables("PivotTable2").PivotFields ("Revenue").Orientation = _ xlHidden With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Payments") .Orientation = xlDataField .Caption = " Payments" .NumberFormat = "#,##0.00_);(#,##0.00)" End With ActiveSheet.PivotTables("PivotTable2").DataPivotFi eld.PivotItems(" Payments"). _ Position = 1 C. Pete Straman -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with a recorded macro to create two pivot tables from same
You are better off to create 2 tables from scratch. If you just copy the
first and paste it, the pivot cache data will be the same and changes made to one table will be replicated in the other. Probably not what you want... HTH "Pete Straman via OfficeKB.com" wrote: All: I recorded a macro to create two pivot tables. I create the second table by copying the first, moving over two columns, paste the data, and them go into the layout to change the data and column fields. When I run the macro it gives me a run time error listed below. How do I change the code to create the second table and fields like I did when I record it? Columns("A:B").Select Selection.Copy Range("C1").Select ActiveSheet.Paste Range("C7").Select Application.CutCopyMode = False ActiveSheet.PivotTableWizard TableDestination:="Sheet1!R1C3" ********* Problem starts here ************* Run-time error €˜1004-: Unable to get the PivotTables property of the worksheet class ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="dosmoyr", _ ColumnFields:="transmoyr", PageFields:="facilityid" ActiveSheet.PivotTables("PivotTable2").PivotFields ("Revenue").Orientation = _ xlHidden With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Payments") .Orientation = xlDataField .Caption = " Payments" .NumberFormat = "#,##0.00_);(#,##0.00)" End With ActiveSheet.PivotTables("PivotTable2").DataPivotFi eld.PivotItems(" Payments"). _ Position = 1 C. Pete Straman -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should let me create pivot tables of non-numeric data. | Excel Discussion (Misc queries) | |||
excel 2007 macro recorded pivot table | Excel Worksheet Functions | |||
How do I create unique pivot tables from the same data | Excel Discussion (Misc queries) | |||
Problem with pivot tables overlapping with new data | Excel Discussion (Misc queries) | |||
problem with macro for pivot tables | Excel Programming |