CODEWITHSUNDEEP
excelvba
Excelsson
Excelsson

Reputation: 195

Excel Custom List to sort PivotTable PivotFields (Columns)

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?

Upvotes: 2

Views: 1578

Answers (2)

Naresh
Naresh

Reputation: 3034

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

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166735

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

enter image description here

Upvotes: 3

Related Questions