I need a modification to a previous post. I've tried changing the code with no success. Where the code refers to Dim filterDate as Date, I need to refer this to cell which contains text.

My file has a pivot field which is a company name which I want to reference from a cell on another sheet. Once this cell changes I want the pivot table to refresh automatically. Any help will be appreciated.

This is the link to the previous post and a solution posted by T-J:

http://www.excelforum.com/excel-prog...ell-value.html


Sub Apply_Date_Filter_From_Worksheet()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterDate As Date
    
    Set pvtTable = Worksheets("Pivot_Sheet").PivotTables("PivotTable1")
    Set pvtField = pvtTable.PivotFields("Date")

    filterDate = CDate(Worksheets("Controls_Sheet").Range("B2"))
    
    On Error Resume Next  'in case date not found
    
    
        For Each pvtItem In pvtField.PivotItems
            If CDate(pvtItem.Value) = filterDate Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
            End If
        Next pvtItem
       
    
    
End Sub