Pivot Tables - using .AutoShow

  • Thread starter Thread starter Paul Martin
  • Start date Start date
P

Paul Martin

I am coding PivotTable creation and want to display only the top 8
items after using .AutoSort and .AutoShow. At the moment, I'm adding
the Row, Column and Data Fields and then .AutoSort and .AutoShow. My
problem is that once I add the DataField, the pivot table overwrites
sheet formatting. Is there a way to apply .AutoShow BEFORE adding the
DataField?

Thanks in advance

Paul Martin
Melbourne, Australia
 
For the benefit of others, I worked out a solution, which is to add
the DataField first, then AutoSort and AutoShow, then add the RowField
and ColumnField. My code looks like this:

Set pt = ws.PivotTables("ptTest")

With pt
.AddDataField .PivotFields("Blah"), "Sum of Blah", xlSum

With .PivotFields("CauseCatType")
.AutoSort xlDescending, "Sum of Blah"
.AutoShow xlAutomatic, xlTop, 8, "Sum of Blah"
End With

.AddFields "CauseCatType", "Category"
End With
 
Back
Top