Pivot table custom sort causes Excel to crash after upgrading to Excel 64 bit

[Murman01]

Hi,

I'm not sure if my question is a general Excel question or if it should be a VBA question. My apologies!

I just recently received a new laptop from my IT Service Desk and it came installed with Excel 2016 32 bit. I subsequently requested Excel 2016 64 bit to be installed as that is what I have used in the past. Now whenever I attempt to refresh a pivot table with a custom sort from within my VBA code, Excel abruptly exits with no error messages provided. I previously had a laptop with Excel 2016 64 bit and there were no problems when running this code. If I edit the VBA code so the custom sort is disabled, the refresh will work, but the results won't be sorted in the desired order.

This is the line of code where Excel will abruptly exit:

Custom_Sort_PivotItems ptField:=pt.PivotFields("Level"), vItems:=Array("1-P", "1", "1-HI", "2", "3", "4", "T", "X", "X-OS", "X-TM")


Any thoughts on why this is happening? Thanks for your help!

 

[GraH - Guido]

Hi, VBA for 32bit or 64bit is different and might well be the cause of the crash. https://docs.microsoft.com/en-us/of...ween-the-32-bit-and-64-bit-versions-of-office
I suggest you upload a file or your code to enable the VBA experts to help you getting the code right.

 

[Murman01]

Hi, I appreciate the information. I am self-taught, so much of my VBA code probably isn't ideal, but it does the trick...or at least it used to until I ran into this issue!

Sub RefreshStatTrackerMonthlySummaryPivotTables()
    Dim Pi As PivotItem
    ActiveSheet.PivotTables("PivotTable1").RefreshTable
    Application.ScreenUpdating = False
   
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Level")
        For Each Pi In .PivotItems
            If Pi.value = "1-P" Or Pi.value = "1" Or Pi.value = "1-HI" Or Pi.value = "2" Or Pi.value = "3" Or Pi.value = "4" Or Pi.value = "T" Or Pi.value = "X" Or Pi.value = "X-OS" Or Pi.value = "X-TM" Or Pi.value = "(blank)" Then
                Pi.Visible = True
            Else
                Pi.Visible = False
            End If
        Next Pi
    End With
       
    Call CustomSortLevelInPivotTable1
        
    Application.ScreenUpdating = True
    ActiveSheet.PivotTables("PivotTable1").RefreshTable
    Worksheets(3).EnableSelection = xlLockedCells
End Sub


Public Sub CustomSortLevelInPivotTable1()
   Dim pt As PivotTable
   Set pt = ActiveSheet.PivotTables("PivotTable1")
       'This procedure will sort the Level drop-down list per the array's custom order as defined below in VItems:=Array(...).
       Custom_Sort_PivotItems ptField:=pt.PivotFields("Level"), vItems:=Array("1-P", "1", "1-HI", "2", "3", "4", "T", "X", "X-OS", "X-TM" )
End Sub

What I'm trying to do is have the pivot table's filter show only certain values and then to sort those certain values in a specific order for the user.

Thank you in advance for the time you've taken to look at this.