Excel 97 - Pivot Table Sorting

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,
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

sebastienmConnect With a Mentor Commented:
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.

jodyjfAuthor Commented:
Thank you, Thank you, Thank you!

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

Thank you for the points, Jody.

Glad i could help,
All Courses

From novice to tech pro — start learning today.