none
Trouble with getting Pivot Tables to automatically sort by custom list after user refreshes RRS feed

  • Question

  • HELP!!!!  Can anyone kidly tell me why my code to automatically sort pivot tables (both by RowField or ColumnField) by a custom list after a refresh  (and depending if they contain specific pivotfields) does not work?????

    ==========================================
    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    ' PROCEDURE TO ENSURE ALL PIVOTS ARE AUTOMATICALLY SORTED ON JG/SG/EC USING CUSTOM LISTS
    '
    ' Declarations
        Dim pvtfield As PivotField
        Dim sRowFieldName As String
        Dim sColFieldName As String
        Dim sType As Long                   '0 for ColumnField, 1 for RowField
    '
    ' Error Handling
        On Error Resume Next
    '
         Application.Enableevents = false

        For Each Target In Sh.PivotTables
            For Each pvtfield In Target.ColumnFields
                sColFieldName = pvtfield.Name
                If InStr(UCase(sColFieldName), "JG") > 0 Or _
                           InStr(UCase(sColFieldName), "JOB G") > 0 Then

                    Call JT_Sort_JG(Target, sColFieldName, 0)
                ElseIf InStr(UCase(sColFieldName), "SG") > 0 Or _
                           InStr(UCase(sColFieldName), "SALARY G") > 0 Then

                    Call JT_Sort_SG(Target, sColFieldName, 0)
                ElseIf InStr(UCase(sColFieldName), "EC") > 0 Or _
                            InStr(UCase(sColFieldName), "ORG LEV") > 0 Then

                    Call JT_Sort_EC(Target, sColFieldName, 0)
                Else: End If
            Next pvtfield
           
            For Each pvtfield In Target.RowFields
                sRowFieldName = pvtfield.Name
                If InStr(UCase(sRowFieldName), "JG") > 0 Or _
                               InStr(UCase(sRowFieldName), "JOB G") > 0 Then

                    Call JT_Sort_JG(Target, sRowFieldName, 1)
                ElseIf InStr(UCase(sRowFieldName), "SG") > 0 Or _
                                InStr(UCase(sRowFieldName), "SALARY G") > 0 Then

                    Call JT_Sort_SG(Target, sRowFieldName, 1)
                ElseIf InStr(UCase(sRowFieldName), "EC") > 0 Or _
                                InStr(UCase(sRowFieldName), "ORG LEV") > 0 Then

                    Call JT_Sort_EC(Target, sRowFieldName, 1)
                Else: End If
            Next pvtfield
        Next Target

       Application.EnableEvents = True
    End Sub


    Sub JT_Sort_SG(ByRef Target As PivotTable, sFieldName As String, lFieldType As Long)
    ' PROCEDURE TO SORT A LIST BY THE JET CUSTOM SALARY GROUP LIST

    ' Declarations:
        Dim SColAddress As String
        Dim sRowAddress As String
        Dim lLength As Long
        Dim lEnd As Long
    '
    ' Error Handling
        On Error Resume Next
    '
        If lFieldType = 0 Then
        'Set Variables
            lLength = Len(Target.ColumnRange.Address)
            lEnd = Application.WorksheetFunction.Find(":", Target.ColumnRange.Address, 1)
            lLength = lLength - lEnd - 1
            SColAddress = Left(Target.ColumnRange.Address, lLength)

        'Sort by SG Level
                ActiveSheet.Range(SColAddress).Sort _
                        Key:=sFieldName, Order1:=xlAscending, Type:=xlSortLabels, _
                        OrderCustom:=CLng([DEV\\getCustomListNum_SG_Levels_List]), _
                        Orientation:=xlLeftToRight
        End If
    '
        If lFieldType = 1 Then
        'Set Variables
            lLength = Len(Target.RowRange.Address)
            lEnd = Application.WorksheetFunction.Find(":", Target.RowRange.Address, 1)
            lLength = lLength - lEnd - 1
            sRowAddress = Left(Target.RowRange.Address, lLength)
               
        'Sort by SG Level
                ActiveSheet.Range(sRowAddress).Sort Key:=sFieldName, Order1:=xlAscending, _
                Type:=xlSortLabels, _
                OrderCustom:=[DEV\\getCustomListNum_SG_Levels_List], _
                Orientation:=xlTopToBottom
        End If
    End Sub

    Wednesday, October 17, 2012 7:42 PM

Answers

  • Felipe:

    Thanks!  I guess I just needed a bit of time away from it.  Unable to upload workbook for security reasons from client, but I think the solution is as follows:

    ActiveSheet.PivotTables(sPivotName).PivotFields(sFieldName).AutoSort xlAscending, "sFieldName"

    Managed to reduce 3 sub-procedures down to one!

    Appreciate  your quick response!!

    Thursday, October 18, 2012 2:22 AM

All replies

  • The code is too complex. Would you mind to upload an example workbook on SkyDrive?

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Wednesday, October 17, 2012 10:03 PM
  • Felipe:

    Thanks!  I guess I just needed a bit of time away from it.  Unable to upload workbook for security reasons from client, but I think the solution is as follows:

    ActiveSheet.PivotTables(sPivotName).PivotFields(sFieldName).AutoSort xlAscending, "sFieldName"

    Managed to reduce 3 sub-procedures down to one!

    Appreciate  your quick response!!

    Thursday, October 18, 2012 2:22 AM
  • Hi Felipe:

    Thanks for posting in the MSDN Forum.

    Did you solved via your snippet?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, October 19, 2012 6:45 AM
    Moderator
  • Tom: Seems so, but will know for certain after I've had a chance to put it through a few rounds of testing. Think it was just a case of making something more complicated then need be...or thinking too much.
    Friday, October 19, 2012 12:53 PM
  • Hi swhgraham,

    I will close it. Please feel free to unmark it if you found something need help on this issue.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, October 22, 2012 5:24 AM
    Moderator