Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Excel 97 - Pivot Table Sorting

Posted on 2003-03-28
Medium Priority
Last Modified: 2013-04-16
I am modifying a vba program that programatically creates a pivot table based on data from our Oracle Database.  The change I made added one field to the row data (there was only one row field and now there are two).  

The pivot table is used to compare data about vehicles.  It compares one vehicle to many others across different quarters.  So, it would go something like this:

Model            ID     2003Q4   2003Q3
Acura CL         01     10        9
Acura CL         02     14        10
Chevy Cavalier   42     84        75
Honda CRV        49     50        47

That is totally fake data, but hopefully it gives you an idea.  The Acura CL model being listed is something that happens rarely, but it does happen and is the reason why I made a change to begin with.  Before model was the only rowfield.  I added the ID field because the pivot table was grouping by the model name and producing bad data in the few instances where a model name appeared more than once in our database.  

After the pivot table is created, the program gives users an option to sort the data in ascending or descending order.  This sort is on the DATA (numbers under the quarter columns).  Now, this worked before I added the second field to the row data.  Now, however, this sort seems to have no effect.  The sorting code looks like this:

    Set pvtPivot = ActiveCell.PivotTable
    If Me.optHightoLow = True Then strSortMethod = xlDescending
    If Me.optLowtoHigh = True Then strSortMethod = xlAscending
    If Selection.Columns.Count > 1 Then
        Selection.Sort Key1:=ActiveCell.End(xlToRight).Address(, , xlR1C1), _
            Order1:=strSortMethod, Type:=xlSortValues, Orientation:=xlTopToBottom
        Selection.Sort Key1:=ActiveCell.Address(, , xlR1C1), _
            Order1:=strSortMethod, Type:=xlSortValues, Orientation:=xlTopToBottom
    End If

Now, this runs and nothing happens, the data is not resorted.  I have also tried to manually sort this by selecting the data, then choosing Data, Sort... from the menus.  

What is interesting is that when I try to do it manually, the form that opens where you choose sort options shows this at the bottom:
"Sort ID in ascending order by Acura CL: 2003Q4: [Data] ".  Now, I really don't want ID sorted at all!  To choose a sort order, I'd want Data, Model (do I have to specify quarter?)  Or, Quarter, Data, Model.  Here there is only an option to specify one field and it seems to try to figure out the rest on its own.

It seems no matter what I do I cannot change this.  Excel shows all the fields have autosort of xlManual, but it still seems it is trying to automatically sort the pivot table and it won't let me overwrite it.

I really appreciate any direction here, I've been trying to fix this for days and it's driving me nuts.  I'm new to working with Pivot Tables, so I'm afraid it's something basic here that I'm missing.

Thanks for your time,
Question by:jodyjf
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
LVL 16

Accepted Solution

sebastienm earned 400 total points
ID: 8225933
What are the fields used in your data source? Is it:
- Model, Id, Quarter, Data
- Model, Id, 2003Q4, 2003Q3
- ????

If  case 1, try something like
   Selection.PivotTable.PivotFields("Model").AutoSort xlAscending, "Sum of DATA"
This will sort based on the sum of
2003Q4 and 2003Q3.


Author Comment

ID: 8227942
Thank you, Thank you, Thank you!

You lead me in the right direction and I was able to fix it.  I really appreciate it.

LVL 16

Expert Comment

ID: 8227955
Thank you for the points, Jody.

Glad i could help,

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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