Pivot Table filter vba

[jtodd]

Hi
some simple code but will not work

Private Sub CommandButton1_Click()
Dim a As String
Dim pt As PivotTable

ThisWorkbook.Worksheets("Pivot").Activate
a = Worksheets("Pivot").Cells(1, 4).Value

For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Shift")
.ClearAllFilters
.CurrentPage = a

 End With
Next

End Sub

Get an error on .CurrentPage = a

Please help

 

[mart37]

Have you add field SHIFT in the section FILTER?

    With pt.PivotFields("Shift")
        .Orientation = xlPageField
        .Position = 1
    End With

What is the value of the var a?
For exame the right notation you can make a pivottable by hand and then you can change the code with:
a = pt.PivotFields("Shift").CurrentPage.Name (without the line ClearAllFilters!)

 

[jtodd]

Thanks for prompt reply
a = d1 on the spreadsheet , what i want is when user enters shift ,either DAYS or NIGHTS, in D1 the filter on the pivot tables change