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

[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

 

[Jim Thomlinson]

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