Excel 2013 VBA Pivot Table Select Only Top 5 Items

Question

I am trying to find a way to use Excel 2013 VBA to only select the Top 5 items in a pivot table. I have tried the following lines of code in an attempt to show only the Top 5 items:

Dim c
dim i as long

Worksheets("sheet1").PivotTables(1).PivotFields ("field1")
ActiveSheet.PivotTables(1).PivotFields("field1").CurrentPage = "(All)"
With ActiveSheet.PivotTables(1).PivotFields("field1")
c = 5
For i = .PivotItems.Count To 1 Step -1
If (c > 0) Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
c = c - 1
Next
End With

This is the code that the macro recorder gives me and does not work:

ActiveSheet.PivotTables("PivotTable5").PivotFilters. _
.PivotFields("field1") Add2 Type:=xlTopCount, _        DataField:=ActiveSheet.PivotTables("PivotTable5"). _
    PivotFields("fied1"), Value1:=5

The code below works fine if there are no value ties for any of the values that are not in the Top 5 list. With our data set we are always going to ties in our Top 5 list.

With Workbooks(cFileName).Worksheets("sheet1")
.PivotTables(1).PivotFields("Field1").PivotFilters.Add2     
xlTopCount, .PivotTables(1).PivotFields("Field1"), 5
End With

EDIT: The pivot table list will show some items as being checked, but no data is associated with those values. The charts which are based on these pivot tables are blank and the following code selects only the bottom 5 values in the field as it was intended to do in it's original post.

With workbook.Worksheets("sheet1").PivotTables("PivotTable2").PivotFields
("Count of Description")

For Each WS In ActiveWorkbook.Worksheets
For Each pvt In WS.PivotTables

c = 5

For i = .PivotItems.Count To 1 Step -1
If (c > 0) Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
c = c - 1
Next
Next
Next

End With

Answer 1

I finally figured this one out. The best way to do it just in case anyone else is wondering, is to record a macro while you:

1. Select the cell in which you want to start seeing data. In my case it was "B10". This will be different for everyone else depending on where your pivot table data is.
2. Press the SHIFT + END + DOWN ARROW Keys to go down to the bottom of the used range.
3. Select all the rows to hide.
4. Right Click on the values to hide.
5. Select "Filter" from the list.
6. Click on "Hide selected items".

Below is the code that I will refer to in the future.

Application.Goto reference:=Workbooks("File").Sheets("Sheet1").Range("B10")
If IsEmpty(Range("B11").value) = False Then
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete
Else
End If