Excel Custom List to sort PivotTable PivotFields (Columns)

Question

I'm currently working on updating a table based on a custom list (Options -> Advanced ->General ->Edit Custom Lists) , however, I would like for that list to automatically update when the range changes.

ie:

Added the custom list from range A1:A20 , so if I change the order of the items the sorting should be automatic.

Based on research it seems that you can only delete the custom list and add a new one, code below:

Application.AddCustomList ListArray:=Range("Sheet1!A1:A20")

I want to use the custom list to compare providers, so pretty much I have a pivot table and I want to get the providers in a certain order from left to right, let's say: Provider A, Provider B, provider C, provider D (That's when Provider A is compared with the rest) but then I want to compare Provider D the list will be like: Provider D, Provider A, Provider B, Provider C, so on and so forth.

Any ideas on alternatives?

As you suggest, you can try to manipulate the Customs List with VBA. If you can tell us what you use the list for we might come up with an alternative. — Variatus
– Variatus, Commented May 25, 2020 at 2:29

Tim Williams · Accepted Answer · 2020-05-25 06:56:21Z

3

https://support.office.com/en-us/article/sort-data-in-a-pivottable-or-pivotchart-e41f7107-b92d-44ef-861f-24430830450a describes multiple ways to sort your pivottable items

One of those is to drag/drop the column headers: if you record a macro while doing that you get something like:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Col2").PivotItems("A").Position = 2

You can loop over the cells in your list and use the values to sort the column headers accordingly.

So for example this will sync up your column headers with the list in M4:M6

Dim pt As PivotTable, pf As PivotField
Dim rngList As Range, i As Long

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Col2")

Set rngList = ActiveSheet.Range("M4:M6")

For i = 1 To rngList.Cells.Count
    pf.PivotItems(rngList.Cells(i).Value).Position = i
Next i

answered May 25, 2020 at 6:56

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Comments

Naresh · Accepted Answer · 2020-05-25 07:16:47Z

1

You don't need a VBA procedure for this if you are willing to spend 2 minutes.

Assumption: In Data Table you have "Provider" Column

Suppose you need "Provider D" Column first in the PivotTable.

In "Pivot" Sheet - Refresh the PivotTable. Click the arrow on the top left for "Column Labels" and select Sot A to Z.

Select "Provider D" Column and drag it to the first position in the PivotTable.

Sub Macro3()
Sheets("Pivot").Select
    FirstCol = InputBox("Enter name of the Provider to appear first in the PivotTable Columns", _
    "First Column Select", "Provider A")

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Provider").AutoSort _
        xlAscending, "Provider"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Provider").PivotItems( _
        FirstCol).Position = 1
End Sub

edited May 25, 2020 at 7:16

answered May 25, 2020 at 5:52

Naresh

3,0372 gold badges11 silver badges16 bronze badges

1 Comment

Excelsson Over a year ago

This did it perfectly, I appreciate it.

Collectives™ on Stack Overflow

Excel Custom List to sort PivotTable PivotFields (Columns)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related