Specify Columns in Pivot Table

[dashiellx]

Pivot table creates like this:

Standard

1 Day

2 Days

3 Days

4 Days

5 Days

10+ Days

8 Days

I need it to be:

Standard

1 Day

2 Days

3 Days

4 Days

5 Days

6 Days

7 Days

8 Days

9 Days

10+ Days

Even if there is no data for the age category

The following is correctly identifying the missing column, but is not creating it:

varPivotItems(0) = "Standard"
varPivotItems(1) = "1 Day"
varPivotItems(2) = "2 Days"
varPivotItems(3) = "3 Days"
varPivotItems(4) = "4 Days"
varPivotItems(5) = "5 Days"
varPivotItems(6) = "6 Days"
varPivotItems(7) = "7 Days"
varPivotItems(8) = "8 Days"
varPivotItems(9) = "9 Days"
varPivotItems(10) = "10+ Days"
For Each varPivotItem In varPivotItems
    Set ptField = .PivotTables("piv" & strPivotSheetName).PivotFields("Age Category")
    bPivotItem = True
    For Each ptItem In ptField.PivotItems
        If ptItem.Name = varPivotItem Then
            bPivotItem = True
            Exit For
        Else
            bPivotItem = False
        End If
    Next ptItem
    If bPivotItem = False Then
        Debug.Print varPivotItem
       .PivotTables("piv" & strPivotSheetName).PivotFields("Age Category").PivotItems.Add varPivotItem
    End If
Next varPivotItem

However, this does not appear to be adding the item nor is the line erroring.

 

[dashiellx]

The code was adding the pivottiems correctly, I just needed to add the following:

.PivotTables("piv" & strPivotSheetName).PivotFields("Age Category").ShowAllItems = True