Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table sorting (excel 97)

    HI ALL,
    This is my coding for a pivot table that can use ONE selected month from a list for the totals column. I however want to sort the pivot table but I don't know how to change the last row of the coding.

    Dim PIVDATA As String
    Application.ScreenUpdating = False
    PIVDATA = Range("SHEET2!A1").Value 'THIS FIELD CONTAINS ANY YEAR/MONTH AVAILABLE
    Range("A17").Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "DETAIL!R17C1:R602C30", TableDestination:="", TableName:="PivotTable2"
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:= _
    "NAAMSA AREA NAME"
    ActiveSheet.PivotTables("PivotTable2").PivotFields (PIVDATA).Orientation = _
    xlDataField
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "'NAAMSA AREA NAME'", _
    xlButton
    ActiveSheet.PivotTables("PivotTable2").PivotFields ("NAAMSA AREA NAME"). _
    AutoSort xlDescending, "Sum of DEC 2000" 'HOW DO I SORT ON PIVDATA INSTEAD OF DEC 2000
    End Sub

    Thanks in anticipation.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table sorting (excel 97)

    Rene,

    Could you post a copy of the workbook with dummy data. I think the solution should be easy to find, once the PT structure can be worked with. It is difficult to recreate the data behind the tabe from the code you posted.

    Andrew

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table sorting (excel 97)

    HI ANDREW
    Attached is an example of a worksheet to explain my problem.

    Thanks a lot
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table sorting (excel 97)

    Rene,

    If you change AutoSort xlDescending, "Sum of DEC 2000" to xlDescending, "Sum of " & PIVDATA it should do what you want, or at least what I think you want. If not let me know.

    If you would like the macro to run everytime you select a month on the Detail sheet, you can place this macro in the detail sheet :

    Private Sub ComboBox1_Change()
    Call Macro2
    End Sub

    To that, just copy the above, right click on the Detail tab, select View Code, and paste.


    Andrew

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table sorting (excel 97)

    Hi There
    Thanks Andrew, you do understand what I want. Unfortunately I get the following error when I chenge the code: APPLICATION-DEFINED OR OBJECT-DEFINED ERROR. I attach my worksheet again for you to see.

    Thanks
    Attached Files Attached Files

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table sorting (excel 97)

    Sorry Rene, I suppose it not really clear, but there is a space in "Sum of " before the final quote mark (after of). Just copy the following line and replcae the existing line.

    ActiveSheet.PivotTables("PivotTable2").PivotFields ("MANUFACTURER").AutoSort _
    xlDescending, "Sum of " & PIVDATA.

    That should work (assuming nothing else has changed)

    Andrew

  7. #7
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table sorting (excel 97)

    Hi Andrew,
    Excellent, thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •