Vba Autosort Pivot Table

[BadTweety]

Hi there,

I am quit new to VBA and i am stuck on a project. So far a succeeded to Create a Pivot table but now i want my Pivot Table to be autosorted.
The tricky part is that my data needs to be sorted on the secondary Row label in Descending order.

Situation:

I have a list of "customers names" (Row Label)
And i want to know how many "Drivers" (secondary Row Label) are going to the same customer and sort the count of them From high to low.
As Value i take the Field "Drivers" once more.

So i want to Sort Customer Name by Count of Drivers in Descending order

When i do it manualy:

click on the filter of Row Labels => More Sort options =>option Descending by: Count Of Drivers

When i record macro:

ActiveSheet.PivotTables("Pivot").PivotFields("Customer Name").AutoSort _
xlDescending, "Count of Drivers"

Code i Made:

Dim PTCache As PivotCache
    Dim pf As PivotField
    
    Dim WSC As Worksheet
    Dim WSI As Worksheet
    Dim PTOutput As Worksheet
    
    Dim PRange As Range

    Set WSC = Worksheets("Data")
    Set PTOutput = Worksheets("test")
    Set WSI = Worksheets("Instructions")

    Dim FinalRow As Long
    Dim FinalCol As Long
    
    FinalRow = WSC.Cells(Application.Rows.Count, 2).End(xlUp).Row
    FinalCol = WSC.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    
    Debug.Print "LastRow=" & FinalRow
    Debug.Print "LastCol=" & FinalCol
    
WSC.Select
    Set PRange = WSC.Range(Cells(1, 1), Cells(FinalRow, FinalCol))
    PRange.Name = "Pivotdata"
        
    'Setup PT Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                    SourceData:="Pivotdata", _
                                                    Version:=xlPivotTableVersion12)
                                                    
    'Create Pivot table
    Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
                                        TableName:="Pivot")
    
    pt.ManualUpdate = True
    
    pt.AddFields RowFields:=Array("Customer Name")
    
    Dim objField As PivotField
    Set objField = pt.PivotFields("Drivers")
    objField.Orientation = xlRowField
  
        
    With pt.PivotFields("Drivers")
    .PivotItems("(blank)").Visible = False
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
    End With
    
    With pt.PivotFields("Date")
        .Orientation = xlPageField
        .Position = 1
        .ClearAllFilters
    End With
       
    pt.ManualUpdate = False

  ActiveSheet.PivotTables("Pivot").PivotFields("Customer Name").AutoSort _
        xlDescending, "Drivers"

    PTOutput.Select
    
End Sub

does anyone know how i can make this work please ?

 

[Jerry Sullivan]

Welcome to MrExcel,

In your autosort statement, why didn't you use "Count of Drivers" as found in the recording?

 

[BadTweety]

Hi there, I did try both "count of drivers" and "drivers" but the same error occurs

 

[Jerry Sullivan]

The error is arising because when that statement runs the ActiveSheet is still "Data".

You could Select sheet "test" first, but it's better to just reference the sheets without selecting them like this...

    PTOutput.PivotTables("Pivot").PivotFields("Customer Name").AutoSort _
        xlDescending, "Count of Drivers"

 

[BadTweety]

The error is arising because when that statement runs the ActiveSheet is still "Data".

You could Select sheet "test" first, but it's better to just reference the sheets without selecting them like this...

Thanks a lot !
that did the job.

 

[Nikanor]

Hi Jerry,
Is this code help me automatically update my pivot table once i have change my source data in a weekly basis?