Run-time error '1004'

[murkaboris]

Hello:

I've recorded a macro in which I'm copy-pasting the same pivot table in the
same worksheet and then changing the display of number inside to show as % of
column but am receiving:

Run-time error '1004' - Unable to get the PivotTables property of the
Worksheet class

The error highlights the following portion of the macro:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Amount")

Here is portion of the macro including the line above that is highlighted
when I click on "Debug":
Range("A4:G14").Select
Range("G14").Activate
Selection.Copy
Range("J4").Select
ActiveSheet.Paste
Columns("J:J").EntireColumn.AutoFit
Columns("K").Select
Selection.ColumnWidth = 8.8
Range("K814").Select
Application.CutCopyMode = False
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Amount")
.Calculation = xlPercentOfColumn
.NumberFormat = "0.00%"
End With

Please help, I'm not too good with macros .
Thank you.

Monika

 

[Dave Peterson]

Just questions...

First, are you sure you're on the correct sheet?

Second, do you have a pivottable that's named pivottable2? You can rightclick
on a pivottable and choose table options to see the name.

And third, do you have a field named "sum of amount" on the pivottable named
pivottable2?

 

[murkaboris]

Hi Dave:

Thank you for responding.

Answers:
1) yes I'm on the correct sheet
2) yes I have a pivot table named "pivottable2" -- that's what it named it
when I copy-pasted the 1st pivot table into this slot (named it "2")
3) so after I copy-pasted the pivottable1 which became pivottable2 it was
exactly the same but instead of regular numbers as displayed in the data
field in pivottable1 I needed it displayed as % of row, which in pivot table
you can achieve in the layout wizard by clickin on your "data field" in this
case the "sum of amount" and selecting under the "Show data as:" the option
"% of column".

Thank you.
Monika

 

[Dave Peterson]

And you verified that the name of that pivottable was still pivottable2, right?
(Maybe you copied|pasted multiple times...)

That said, I only see those three problems that can happen on this line:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Amount")

I'm hoping that you didn't verify the name--because I'm out of ideas.

Sorry.

ps.

If you test with:
With ActiveSheet.PivotTables(1).PivotFields("Sum of Amount")
and then
With ActiveSheet.PivotTables(2).PivotFields("Sum of Amount")

Do either fail?

If yes, then I'm sure it's a naming problem.

If no, then I'm still out of guesses...

 

[murkaboris]

Dave:

I double checked the names and its correct. I copied the pivot once and
pasted it next to it....do you think its the issue with the "% of column", it
shouldn't --- just not sure.

Any general recommendation you can give me when I want to save some memory
by using the same source data for pivot so I normally would copy - paste the
same pivot next to each other and change the fields where I need to by going
to the layout wizard....am I doing it right when I'm recording or should I do
something else....?

thank you.
Monika

 

[Dave Peterson]

I tried a very small test and it worked fine for me.

I'm not sure what the problem is, so I can't offer any meaningful suggestions.

Sorry.

Dave:

I double checked the names and its correct. I copied the pivot once and
pasted it next to it....do you think its the issue with the "% of column", it
shouldn't --- just not sure.

Any general recommendation you can give me when I want to save some memory
by using the same source data for pivot so I normally would copy - paste the
same pivot next to each other and change the fields where I need to by going
to the layout wizard....am I doing it right when I'm recording or should I do
something else....?

thank you.
Monika