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

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

837 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