?
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
Medium Priority
?
1,162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

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