We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Pivot Table recorded macro fails

jason987
jason987 asked
on
Medium Priority
511 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

What happens when you try this?

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

Sid

Author

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

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

Sid

Author

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.
Jason, May I see the Excel File?

Sid

Author

Commented:
Here I made a copy since my original is huge.  It examples the problem though. (see attached macro)
testbook---Copy.xlsm

Author

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.

Author

Commented:
I don't know why I keep saying "formula" when I mean "calculated field".
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
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

Author

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).
I have checked other link as well and they all recommend to delete it.

Still searching.

Sid
Requesting other experts to jump in :)

Sid

Author

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.
The points are not that important. The solution is and hence I have requested other experts to join :)

Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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.
Nice One :)

Sid

Author

Commented:
Awesome, thanks a lot!

Author

Commented:
Solved an issue that is giving many experts fits!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.