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
Bookmarks