View Full Version : Pivot find and replace

05-24-2016, 12:44 PM

I need some help with a few pivots I am creating using vba.

I'm facing the following issues with my pivots:

1) Not being able to sort in descending order basis the values in the grand total. I have tried using the recorded code as well as written code on my own, but it does not seem to work
2) I want to replace certain values within the pivots. But I can't be sure of the position of these values. I think find and replace would work, but I am not sure on how to put them in the code.

code is in the excel file. I can't attach the data, since its confidential. I have attached the pivot without the source, to show my problem.

Please help.

Attaching the file for reference.

05-24-2016, 02:42 PM
right-click on center header, choose More Sort options…
16243You won't be allowed to change values, only labels.

05-25-2016, 12:48 AM
Thanks p45cal but i need to do this through vba.

any thoughts?

05-25-2016, 01:59 AM
Here's the macro I recorded:
Sub Macro1()
ActiveSheet.PivotTables("PivotTable1").PivotFields("center").AutoSort xlDescending, "Count of center"
End Sub