[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

Pivot Table recorded macro fails

Hello,

I am having an issue I can't seem to resolve.  I can't seem to remove, via VBA, a pivot field that is a formula.  I recorded this macro of me adding and removing it and I turn around and run it and it fails on the removal(xlHidden) with a 1004:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Rate - OF - Error"), _
        "Sum of Rate - OF - Error", xlSum
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Rate - OF - Error") _
        .Orientation = xlHidden
End Sub

Open in new window


One would think that I should add (or the macro record) "sum of" to the string for removal but that doesn't work either.

Any help would be appreciated.
0
jason987
Asked:
jason987
  • 10
  • 9
  • 2
1 Solution
 
SiddharthRoutCommented:
What happens when you try this?

ActiveSheet.PivotTables("PivotTable1").PivotFields("Rate - OF - Error").Orientation = xlHidden

Sid
0
 
jason987Author Commented:
Did you mean to put something else?  That's the same statement as above.
0
 
SiddharthRoutCommented:
Jason, The difference is of Space before the underscore and ")".

ActiveSheet.PivotTables("PivotTable1").PivotFields("Rate - OF - Error") _
        .Orientation = xlHidden

Sid
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jason987Author Commented:
I don't think I am following you.  There must be a space before a line continuation "_".

If I remove the continuation and make it one line it still fails with the same error.
0
 
SiddharthRoutCommented:
Jason, May I see the Excel File?

Sid
0
 
jason987Author Commented:
Here I made a copy since my original is huge.  It examples the problem though. (see attached macro)
testbook---Copy.xlsm
0
 
jason987Author Commented:
I found this, which seems to imply that there is no way to do it without removing the formula:

http://www.pivot-table.com/excel-pivot-tables/pivot-table-calculated-field-removed-with-excel-vba

If you have a way I can do it without a "hack" approach I'll give you the points.
0
 
jason987Author Commented:
I don't know why I keep saying "formula" when I mean "calculated field".
0
 
SiddharthRoutCommented:
Yeah I understood :)

Yes, if the field is already present then the first line will give you the error. otherwise it will work just fine. I am looking into the 2nd line of the code.

Sid
0
 
SiddharthRoutCommented:
Ok Use this instead of the 2nd line

ActiveSheet.PivotTables("PivotTable1").PivotFields("Rate - OF - Error").Delete

So your new code should look like this

Sub Sample()
    On Error Resume Next
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Rate - OF - Error"), "Sum of Rate - OF - Error", xlSum
    On Error GoTo 0
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Rate - OF - Error").Delete
End Sub

Open in new window


Sid
0
 
jason987Author Commented:
Yea, that's what the article says but if I delete it, I'm not hiding it, I'm removing it from the entire workbook (to include other pivots).
0
 
SiddharthRoutCommented:
I have checked other link as well and they all recommend to delete it.

Still searching.

Sid
0
 
SiddharthRoutCommented:
Requesting other experts to jump in :)

Sid
0
 
jason987Author Commented:
Thanks for the help.  I'm thinking there's really no application function to do it in a sensible way.  If no one else has a better solution (not deleting the calc. field) the points are yours.
0
 
SiddharthRoutCommented:
The points are not that important. The solution is and hence I have requested other experts to join :)

Sid
0
 
Rory ArchibaldCommented:
Use this instead:

    With ActiveSheet.PivotTables("PivotTable1").DataFields("Sum of Rate - OF - Error")
        .Parent.PivotItems(.Name).Visible = False
    End With

Open in new window

0
 
jason987Author Commented:
Wow that seems to work!  Do you mind briefly explaining or linking me to how that works that is different than the way I am trying?
0
 
Rory ArchibaldCommented:
It refers to the pivotfield as an item of the Values data field rather than as a pivotfield per se (if you look at the Orientation of the Pivotfield for the calculated field it's already hidden, oddly enough.) That's the same method as the Values dropdown uses internally I suspect - rather like the dropdown in any other pivot field.
0
 
SiddharthRoutCommented:
Nice One :)

Sid
0
 
jason987Author Commented:
Awesome, thanks a lot!
0
 
jason987Author Commented:
Solved an issue that is giving many experts fits!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now