Problem with a recorded macro to create two pivot tables from same data

  • Thread starter Thread starter Pete Straman via OfficeKB.com
  • Start date Start date
P

Pete Straman via OfficeKB.com

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").DataPivotField.PivotItems("
Payments"). _
Position = 1


C. Pete Straman
 
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
 
Back
Top