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.
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.