Sort Pivot Table rows by Range list using VBA

MarkCBB

Active Member
Joined
Apr 12, 2010
Messages
497
HI there,

I currently have a few pivot tables that get updated weekly with more information.

However every time I update the pivot tables, I end up re sorting all of them by moving the rows with the Drag function.

I also have a list of the row items in another Range (I manually type, i.e. from sheet11.range(A2) and downwards). the order that the items are sorted in this this is the order I sort the Pivot row Items in each of the pivot tables.

I am looking for some Code to sort 1 Pivot table according to the list.

I should be able to adapt this to all the other relevant Pivot Tables.

thank you.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
This is the code that I come up with, but it does work, and the strange thing is that I dont get any errors.

Code:
Sub SortPivotTables()
Dim I As Integer
Dim INextWeekName As Integer
I = Sheet11.Range("E1").Value - 6 'The value in E1 is the number if Items in the field, the 6 is because I only want to show 6 of them at anyone time.
INextWeekName = 0
Do
Sheet10.PivotTables(1).PivotFields("WEEK").PivotItems(Sheet11.Range("C2").Offset(INextWeekName, 0).Value).Position = I
I = I + 1
INextWeekName = INextWeekName + 1
Loop Until I = Sheet11.Range("E1").Value

End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top