Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel VBA for Pivot Table cleaning up recorded code for Top N and sorting

Status
Not open for further replies.

makeitwork09

Technical User
Sep 28, 2009
170
US
I am using Windows 2007 and Excel 2007

I used the Macro recorder for a pivot table. I cleaned up most of the code to not use what Microsoft generates, but I am not sure how to clean up the code for a top 10 filter and for sorting values in descending order. Below is what the recorder generated, that I want to change.

Code:
ActiveSheet.PivotTables("PivotTable3").PivotFields("CM Classification2"). _
        AutoSort xlDescending, "Sum of Carrying Value", ActiveSheet.PivotTables( _
        "PivotTable3").PivotColumnAxis.PivotLines(1), 1

ActiveSheet.PivotTables("PivotTable3").PivotFields("loan_xref").PivotFilters. _
        Add Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable3"). _
        PivotFields("Sum of Carrying Value"), Value1:=25

I want to add the above to the follow with block

Code:
    With TL
          
        'construct layout
        .ShowDrillIndicators = False
        .RowAxisLayout xlTabularRow
        .TableStyle2 = ""
        .NullString = "0"
    
        .AddFields RowFields:=Array("loan", "Name / ID", "Classification")
        
        With .PivotFields("Carrying Value")
                .Orientation = xlDataField
                .Function = xlSum
                .Caption = "Statement Value"
                .NumberFormat = "$#,##0_);($#,##0)"
                .Position = 1
        End With

     
        With TL.PivotFields("loan")
            .PivotItems("(blank)").Visible = False
        End With
        
        'calculate the pivot table
        TL.ManualUpdate = False
        TL.ManualUpdate = True

    
    End With

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top