• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1757
  • Last Modified:

When double clicking a pivot table, how do I get it to stay in the same sort order as the data sheet?

In Excel 2010, when I create a pivot table and double click to drill down into the detail, the new data sheet that opens does not remain in the same sort order as my original source data.  How can I get the data to appear in the same sort order as the original source data?
The field that I have sorted in descending order in my source data is not a field in the pivot table, but as you know, when the pivot table is double clicked, it shows all of the fields of data from the original source.

Thanks!
0
angelfromabove
Asked:
angelfromabove
  • 2
  • 2
1 Solution
 
AgneauCommented:
Hello angelfromabove,

The only way to achieve what you want is by using VBA.
MS-Excel has the ability to "memorize" the last sort command used per worksheet. Due to this, the original source interval must be the only one sorted on that specific worksheet, otherwise the solution proposed will fail.

So let's see how it works:

Open the VBA project and create a new module containing the following lines

Public keys_sort() As String
Public pvtdoubleclicked As Boolean

Open in new window


Now double click the object ThisWorkbook and copy and paste the following lines:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim i As Integer
    Dim s() As String
    
    ' When a new sheet is activated, we need to check if our global variable is set to true, if yes a pivot table was double clicked...
    If pvtdoubleclicked Then
        
        '.. and now let's apply the sort key to the data set returned
        For i = UBound(keys_sort) To LBound(keys_sort) Step -1
            s = Split(keys_sort(i), "*")
            ActiveCell.CurrentRegion.Sort key1:=ActiveSheet.Cells(1, Val(s(0))), Order1:=Val(s(1)), Header:=xlYes
        Next
        pvtdoubleclicked = False
    End If
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim rng As Range
    Dim key_sort As SortField
    Dim i As Integer
    
    ' If the cell double-clicked is not a pivot table a run time error will occur, so
    ' let's avoid this with On Error statement
    On Error GoTo ExitPoint

    ' Set rng to pivot table source data
    Set rng = Application.Range(Application.ConvertFormula(Target.PivotTable.SourceData, xlR1C1, xlA1))
    
    ' Set pvtdoubleclicked to true, so the spreadsheet project will know that a pivot table was double-clicked
    ' when a new worksheet is created
    pvtdoubleclicked = True
    
    ' Read and store the sort keys used by pivot table data source
    ReDim keys_sort(rng.Worksheet.Sort.SortFields.Count - 1)
    
    i = 0
    For Each key_sort In rng.Worksheet.Sort.SortFields
        keys_sort(i) = key_sort.key.Column - rng.Column + 1 & "*" & key_sort.Order
        i = i + 1
    Next
    
    Exit Sub

ExitPoint:
pvtdoubleclicked = False

End Sub

Open in new window


Be sure that your source data is sorted and try to double-click the pivot table.

Let me know if this works for you.

Regards
0
 
angelfromaboveAuthor Commented:
Can you please let me know which part of this code I have to change to fit my data? I don't understand all of the code.

Thanks!
0
 
AgneauCommented:
Hi

The code is generic and should work in every spreadsheet without any modification.
I'm assuming that you have some basic VBA knowledge. If that is not the case let me know and I'll give you detailed steps how to implement it in your spreadsheet.

Regards
0
 
angelfromaboveAuthor Commented:
Thanks, I do have VBA knowledge, I'll try it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now