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
712 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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 the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 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

20 Experts available now in Live!

Get 1:1 Help Now