none
Excel 2010, any way to use VBA to sort the Grand Total column of a Pivot table? RRS feed

  • Question

  • I'm using VBA to build pivot tables and want to be able to sort the grand total column after the table is created. The number of columns will change from run to run so I can't hard code a cell address.

    I'm currently checking what I know to be the rightmost cell I'll ever have in the table and if it's blank, moving one cell left until I hit a cell with data and then sort on that column. I could also go one or more columns further right than I'll ever see then use and <end><left> but I'd much prefer to use a named reference.

    I can't find anything in help and a search of this forum and the web hasn't turned up a reference.

    Thanks for any information you can provide.

    Doug

    Thursday, September 11, 2014 1:22 PM

Answers

  • So I've been poking around and ran across this post:

    ozgrid dot com / forum/showthread.php?t=58543

    and the code:

    Sub findString()
    Dim rngX As Range
        
        Set rngX = Worksheets("MySheetName").Range("A1:L5").Find("Grand Total", lookat:=xlPart)
        If Not rngX Is Nothing Then
            MsgBox "Found at " & rngX.Address
        End If

    End Sub

    If I set the range such that I include the top row of the table, exclude the bottom row of the table, and extend the column far enough to the right so I'm assured I get the cell containing the string 'Grand Total', then rngX.Address returns the cell containing that value. I can then use that as the point I go to to start the sort and leave out all the 'if/then/else/butonly/except/opps/notthat' code. I think this is a working and relatively elegant solution.

    Thank you all for your feedback and ideas.

    • Marked as answer by Dogubob Monday, September 15, 2014 4:32 PM
    Monday, September 15, 2014 4:32 PM

All replies

  • Hello,

    Maybey this can help?

    Dim pt As PivotTable, pf As PivotField, pfData As PivotField

    Set pt = ActiveSheet.PivotTables("Draaitabel1")
    Set pf = pt.PivotFields(1)
    Set pfData = pt.DataFields(1)

     Call pf.AutoSort(xlAscending, pfData.Name)

    Als you can get the reference of your rowgrand/colmungrand by:

     pt.RowRange
     pt.ColumnRange

    Best regards

    Thursday, September 11, 2014 2:06 PM
  • No joy. My code is posted below and the sorting does not work for the Grand Total column. The code does not error out but I'm not getting the sort I want.

        Sheets("pvt-CompByProg").Select
        Range("D5").Select
        Set pt = ActiveSheet.PivotTables("PivotTable1")
        Set pf = pt.PivotFields(1)
        Set pfData = pt.DataFields(1)
        Call pf.AutoSort(xlDescending, pfData.Name)

    I'm also confused about the debugger. I would expect I could step through the code and see an actual value for the variable pt.PivotFields(1). When I look at the items listed under 'pt', PivotFields is not there. Neither can I see pd.DataFields(1) as a listing under 'pt'.

    pt.RowRange and pt.Column range do exist but I don't understand how to use them to force a sort on the grand total column.

    In case you can't tell, I'm relatively new to doing anything serious in VBA and my understanding of the object model and debug tools is rather limited. Any pointers or tips are appreciated.

    Thursday, September 11, 2014 3:13 PM
  • Until I can find a method that is able to sort the Grand Totals column by some kind of baked in reference, I opted to use too much code to fix the problem.

    In this sheet, there are multiple pivot tables, some with filters, some with many so the starting row of the pivot table changes based on the particular pivot table.

    I figured out that the 'PivotLines()' parameter is based on the column: B=2, C=3, etc.

    This code goes to the known top row of pivot table data, moves several cells to the right, then starts moving left until data is found. That cell will (should) always be the top row of the Grand Totals and exactly what I want to sort. Very inelegant but it works unless an until the pivot filter structure changes.

    This is an example of one of the routines:

        Sheets("pvt-TigL2ByDesc").Select
        If ActiveSheet.Range("E6").Value <> "" Then
            Range("E6").Select
            linNum = 4
            ElseIf ActiveSheet.Range("D6").Value <> "" Then
                Range("D6").Select
                linNum = 3
                ElseIf ActiveSheet.Range("C6").Value <> "" Then
                Range("C6").Select
                linNum = 2
                    ElseIf ActiveSheet.Range("B6").Value <> "" Then
                    Range("B6").Select
                    linNum = 1
        End If
        ActiveSheet.PivotTables("PivotTable4").PivotFields("PN-Desc").AutoSort _
            xlDescending, "Count of Comp-RMANumber", ActiveSheet.PivotTables("PivotTable4") _
            .PivotColumnAxis.PivotLines(linNum), 1

    Thursday, September 11, 2014 6:37 PM
  • Hello,

    Was just thinking: to sort your grand total, you need to sort your colmun or row field according to a calulated item, right?

    eg. PivotTables("PivotTable1").PivotFields("MONTH").AutoSort     xlAscending, "Tonnage"

    This wil sort my column field according to my calculated item and so also sort the grand total

    Monday, September 15, 2014 9:38 AM
  • Hello,

    Was just thinking: to sort your grand total, you need to sort your colmun or row field according to a calulated item, right?

    eg. PivotTables("PivotTable1").PivotFields("MONTH").AutoSort     xlAscending, "Tonnage"

    This wil sort my column field according to my calculated item and so also sort the grand total

    That won't work (unless I'm missing something; I've worked with pivot tables forever but am just now trying to build them using VBA so the object model is not something I understand yet). Assume that I have a pivot table with 5 months as columns plus a grand total column and the data going down the rows is 'product name' with sum of tonnage in the data cells.

    In month 1, the first three products have tonnage, in the next month the 1st, 3rd, and 12th have data, etc. If I sort on any one month the sort will not be the same as sorting on the grand total. So if I just say sort tonnage, does that look at total tonnage for all data or tonnage for some specific month?

    Monday, September 15, 2014 4:27 PM
  • So I've been poking around and ran across this post:

    ozgrid dot com / forum/showthread.php?t=58543

    and the code:

    Sub findString()
    Dim rngX As Range
        
        Set rngX = Worksheets("MySheetName").Range("A1:L5").Find("Grand Total", lookat:=xlPart)
        If Not rngX Is Nothing Then
            MsgBox "Found at " & rngX.Address
        End If

    End Sub

    If I set the range such that I include the top row of the table, exclude the bottom row of the table, and extend the column far enough to the right so I'm assured I get the cell containing the string 'Grand Total', then rngX.Address returns the cell containing that value. I can then use that as the point I go to to start the sort and leave out all the 'if/then/else/butonly/except/opps/notthat' code. I think this is a working and relatively elegant solution.

    Thank you all for your feedback and ideas.

    • Marked as answer by Dogubob Monday, September 15, 2014 4:32 PM
    Monday, September 15, 2014 4:32 PM