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

  • Thread starter Pete Straman via OfficeKB.com
  • 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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top