makeitwork09
Technical User
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.
I want to add the above to the follow with block
Thanks
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