CODEWITHSUNDEEP
excelpivot-tablevba
t.mo
t.mo

Reputation: 263

Is it possible to have a pivot table slicer sort largest to smallest?

I have created a new table with Gain and Loss. My goal is to have gain sort the table from largest to smallest & Loss sort smallest to largest. I have tried some vba, but unable to sort the value.

Sub Macro1() 

Dim wb As Workbook 
Dim ws As Worksheet 
Dim targetTable As ListObject 
Set wb = ThisWorkbook 
Set ws = wb.Worksheets("Project Dash") 

If Worksheets("Project Dash").SlicerCaches("Slicer_Gains__Losses") .SlicerItems("Gai‌​ns").Selected = True Then 

    ActiveSheet.PivotTables("ProjectGain20").PivotFields("Projec‌​t").AutoSort _ xlDescending, "Sum of Work Variance", ActiveSheet.PivotTables("ProjectGain20"). _ PivotColumnAxis.PivotLines(1), 1 

End If 

End Sub

Upvotes: 1

Views: 2661

Answers (1)

QHarr
QHarr

Reputation: 84465

Here is an example using Option buttons to sort the backing table instead. A slicer is meant to filter your data not to order it. When you add the form control radio/option buttons you need to edit the text, as shown in my image, and then right click each one and associate each button with the macro CheckOptions. Here the field being sorted is [Value1] which you would change as appropriate. Your data needs to be set up as a table, as shown in the image.

I have used a suggestion by @L42 to do a check on which option button called the macro.

The sort order enumerations are given here.

Table set up and form control option buttons

Code to go in a standard module:

Option Explicit

Public Sub CheckOptions()                        

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim targetTable As ListObject

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1") 'Change as appropriate
    Set targetTable = ws.ListObjects("Table1")

    Select Case Application.Caller

    Case "Option Button 1"

        SortTable targetTable, 2

    Case "Option Button 2"

        SortTable targetTable, 1

    End Select
End Sub

Private Sub SortTable(ByRef targetTable As ListObject, ByVal myOrder As Byte) 'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlsortorder-enumeration-excel

    With targetTable.Sort

        .SortFields.Clear

        .SortFields.Add _
        Key:=Range("Table1[[#All],[Value1]]"), SortOn:=xlSortOnValues, order:= _
                   myOrder, DataOption:=xlSortNormal

        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

    End With

End Sub

Upvotes: 1

Related Questions