PDA

View Full Version : [SOLVED] Pivots auto sort and dollar sign needs to be added



Hudson
02-07-2017, 08:50 AM
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
02-07-2017, 10:50 AM
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
02-07-2017, 11:33 AM
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
02-07-2017, 01:10 PM
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
02-07-2017, 10:00 PM
Hi Paul,

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

I really appreciate your effort .