How to group aging Column in chronological order in Pivot table

Help Studio
, ,
1

I have attached an excel file with sheet1 and sheet2 , in sheet 2 , I have Pivot table created for test purpose and I want to arrange order in choronological order like 0-30, 31-60, 60-90, 90-120 ,120-180, 180-365, >365

I tried with invoke VBA for assending but it didnt work. if possible please create xaml and attach it. thanks

test.xlsx (13.6 KB)

Forum Engagement Daily Reports
2

@Syed_Sultan_Ahmed1

I was tried by using some llm
this giving me the what exactly your looking for

VB code

Sub CreateAgingPivotWithCustomSort()

Dim wsData As Worksheet
Dim wsPivot As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim dataRange As Range
Dim lastRow As Long
Dim pivotStart As Range

' === Setup Source Data Sheet ===
Set wsData = ThisWorkbook.Sheets("Sheet1") ' Change if needed

' === Create/Reset Pivot Sheet ===
On Error Resume Next
Set wsPivot = ThisWorkbook.Sheets("Pivot")
If wsPivot Is Nothing Then
    Set wsPivot = ThisWorkbook.Sheets.Add(After:=wsData)
    wsPivot.Name = "Pivot"
Else
    wsPivot.Cells.Clear
End If
On Error GoTo 0

' === Define Data Range ===
lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
Set dataRange = wsData.Range("A1:C" & lastRow)

' === Create Pivot Cache & Table ===
Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataRange)
Set pivotStart = wsPivot.Range("A3")
Set pvt = pvtCache.CreatePivotTable(TableDestination:=pivotStart, TableName:="AgingPivot")

' === Configure Pivot Fields ===
With pvt
    .PivotFields("Supplier Name").Orientation = xlRowField
    .PivotFields("Aging category").Orientation = xlColumnField
    .AddDataField .PivotFields("Open number"), "Sum of Open number", xlSum
End With

' === Add Custom List to Sort Aging Buckets ===
Application.AddCustomList ListArray:=Array("0-30", "30-60", "60-90", "90-120", "120-180", "180-365", ">365")

' === Sort the Aging Buckets ===
With pvt.PivotFields("Aging category")
    .AutoSort xlManual, "Aging category"
    .AutoSort xlAscending, "Aging category"
End With

' === Format & Autofit ===
wsPivot.Columns.AutoFit

MsgBox "Pivot table created successfully in 'Pivot' sheet!", vbInformation

End Sub

Steps i followed

  • Open Excel and press ALT + F11 to open the VBA Editor
  • Insert a new module: Insert > Module
  • Paste the code above
  • Close the editor
  • Run macro: ALT + F8 → select CreatePivotAndSortAgingBuckets

if you find it helpful, mark this post as solution.

Happy automation!

3

thanks Its working VBA

4

@Syed_Sultan_Ahmed1

Glad to hear.

But one thing,

i think you mistakenly marked your post solution, that not standard way
go to my reply there you can select as solution.

1 Like
5

sure and one thing before we close it …

file should be Macro enabled in UiPath when we run it..using invoke VBA
File to be update should be .txt or .vbs

6

I am not getting you..
Can you explain more

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.