Pivot Tables - using .AutoShow

[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

 

[Paul Martin]

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