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


C. Pete Straman

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

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").DataPivotField.PivotItems("
> Payments"). _
> Position = 1
>
>
> C. Pete Straman
>
> --
> Message posted via http://www.officekb.com
>