Pivots auto sort and dollar sign needs to be added

[Hudson]

Hi all,

I have this small code that creates a basic pivots with the source data available . everything is fine but I unable to " Sort " and assign a dollar sign to it . I tried various ways using online . so thought I could reach to place I get a advice .

below is the code for your reference .

Sub pivot_table_code2()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Raw data").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("A4"))
With .PivotFields("Credit analyst")
     .Orientation = xlRowField
     .Position = 1
End With
.AddDataField .PivotFields("Overdue Total"), "Sum of overdue total", xlSum
.AddDataField .PivotFields("90+"), "Sum of 90+", xlSum
End With
With ActiveSheet.PivotFields("Credit analyst")
.AutoSort Order:=xlDescending, Field:="90+"
End With

End Sub

and attached is the file with my wish pivot I wanted to be using the code all the time.

any inputs are appreciated.

[Paul_Hossler]

1. the sort order seems to be manual

2. I like the PT wizard method since I think it's easier and clearer to use

Option Explicit
Sub Macro4()
    Dim pt As PivotTable
    
    'expression.PivotTableWizard( _
    '   SourceType, SourceData, TableDestination, TableName,
    '   RowGrand, ColumnGrand, SaveData, HasAutoFormat,
    '   AutoPage, Reserved,
    '   BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)
    Set pt = Worksheets("Summary").PivotTableWizard( _
        xlDatabase, Sheets("Raw data").Range("A1").CurrentRegion, Worksheets("Summary").Range("A4"), "SummaryPT", _
        False, True, True, False)
    
    With pt
        With .PivotFields("Credit analyst")
            .Orientation = xlRowField
            .Position = 1
            .PivotItems("James").Position = 1
            .PivotItems("danny").Position = 2
            .PivotItems("ramu").Position = 3
            .PivotItems("Hudson").Position = 4
            .PivotItems("Jack").Position = 5
            .PivotItems("Maddy").Position = 6
        End With
    
        .AddDataField .PivotFields("Overdue Total"), "Sum of Overdue Total", xlSum
        .AddDataField .PivotFields("90+"), "Sum of 90+", xlSum
        
        .PivotFields("Data").Orientation = xlColumnField
        
        .PivotFields("Sum of Overdue Total").NumberFormat = "$#,##0.00_);([red]$#,##0.00)"
        
        .PivotFields("Sum of 90+").NumberFormat = "$#,##0.00_);([red]$#,##0.00)"
    
        .TableRange2.EntireColumn.AutoFit
    End With
End Sub

[Hudson]

Hi Paul,

Thank you so much for your time. this is very good but this is not what I am looking for . besides my column (90+) is not sorted xldescending . can we make it with out wizard please .

thanks you once again.

[Paul_Hossler]

If you use the macro recorder, you can get the syntax and then integrate it into your overall macro

Sub pivot_table_code2()

    With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Raw data").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("A4"))

        With .PivotFields("Credit analyst")
             .Orientation = xlRowField
            .Position = 1
        End With

        .AddDataField .PivotFields("Overdue Total"), "Sum of overdue total", xlSum
        .AddDataField .PivotFields("90+"), "Sum of 90+", xlSum
    
        .PivotFields("Sum of Overdue Total").NumberFormat = "$#,##0.00_);([red]$#,##0.00)"
        .PivotFields("Sum of 90+").NumberFormat = "$#,##0.00_);([red]$#,##0.00)"
        
        With .PivotFields("Credit analyst")
            .AutoSort Order:=xlDescending, Field:="Sum of 90+"
        End With
    End With

End Sub

[Hudson]

Hi Paul,

Perfect , this is what i am looking for . thanks for your time mate .

I really appreciate your effort .