Sorting Fields and Values in Pivot Table

Question

I need a little help with macro that can sort the data in the below pivot table.

I have a macro that copies Excel ranges from a few spreadsheets, then pastes the data into destination workbooks and updates multiple pivot tables.

Each time when I change a filter to the new month in the below pivot table, everything is sorted in the alphabetical order. As I need to change a filter on a monthly basis, I would like to ensure that the data is in the required order in this specific pivot table.

I would like to sort the pivot table by values in descending order but always only in the 4th column (for the last month - in this case for October). It needs to be done for both - countries and cities.

Original Pivot Table:

Required order of the pivot table:

I tried to record the macro but no success.

"I tried to record the macro but no success." This is very meaningless. What did you record? Show the code you tried. Why exactly didn't that work? What did it do instead of what you expected? — Pᴇʜ
– Pᴇʜ, Commented Oct 18, 2018 at 6:02
Thanks for getting back to me Pᴇʜ and editing my post. I'm quite new to this forum so thanks also for your advises. Regarding the above query; it seems that I recorded the macro moved and my pivot table into new tab thereafter. That's why it didn't work The good news is, I finally managed to get this working. — SeT
– SeT, Commented Oct 18, 2018 at 8:44
Please don't use comment for code. If it belongs to your question edit the question and add it there. If it is a solution, post it as an answer and mark your own post as solution (may need to wait some time until you can vote your post). — Pᴇʜ
– Pᴇʜ, Commented Oct 18, 2018 at 9:05
Thanks Pᴇʜ. I will be able to mark this as a solution tomorrow. — SeT
– SeT, Commented Oct 18, 2018 at 9:14

SeT · Accepted Answer · 2018-10-18 09:10:44Z

1

The below code worked for me:

ActiveSheet.PivotTables("PivotTable1").PivotFields("COUNTRY").AutoSort _
    xlDescending, "Sum of Amount", ActiveSheet.PivotTables("PivotTable1"). _
    PivotColumnAxis.PivotLines(3), 1

ActiveSheet.PivotTables("PivotTable1").PivotFields("CITY").AutoSort _
    xlDescending, "Sum of Amount", ActiveSheet.PivotTables("PivotTable1"). _
    PivotColumnAxis.PivotLines(3), 1

Thanks for your help in this matter Pᴇʜ

answered Oct 18, 2018 at 9:10

SeT

2243 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting Fields and Values in Pivot Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related