Solved

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

Posted on 2013-06-23
4
770 Views
Last Modified: 2013-10-01
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
Comment
Question by:angelfromabove
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:Agneau
ID: 39270924
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
 

Author Comment

by:angelfromabove
ID: 39282371
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
 
LVL 2

Accepted Solution

by:
Agneau earned 500 total points
ID: 39282589
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
 

Author Comment

by:angelfromabove
ID: 39283039
Thanks, I do have VBA knowledge, I'll try it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now