Link to home
Start Free TrialLog in
Avatar of Software Engineer
Software Engineer

asked on

Excel: Macro to Contain Sorting for a Pivot Table

Hello:

I would include the Excel workbook, in this case.  But, it contains sensitive information.  So, I'll try to explain my need as best as I can.

Below is code that I have as my macro, in my Excel workbook.  This macro automatically refreshes the pivot table, found in the "UserAccess" tab of the workbook.

I'd like to have this macro, also, contain the means to sort the data in the pivot table that resides in the first column and do so in ascending alphabetical order.  The field is called "User ID".

There is no "ending row", for the data in this pivot table.

What line do I need to add to my code below, in order to make this happen?

Thank you!
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi John,

Try below:
Sub PivotTableSort()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("UserAccess").PivotTables("PivotTable1")

PvtTbl.PivotFields("User ID").AutoSort Order:=xlAscending, Field:="User ID"

End Sub

Open in new window

Avatar of Software Engineer
Software Engineer

ASKER

Shoot!  My code didn't come through, in my post.  It's below.  Can you please tell me how to "marry" my code with yours?

Thanks, Shums!

John

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Worksheets("UserAccess").PivotTables("UserAccess").PivotCache.Refresh
    Application.EnableEvents = True
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked!  Thank you, Shums!

John
You're Welcome John! Pleased to help :)