Pivot table search

[jcaptchaos2]

i am trying to make it so a user can type something in a cell press a macro button and the pivot table will filter for that word. I can do it manually by selecting the drop down, selecting label filters and typing my search word in. I want the user to be able to type the search word in cell B1 hit the button and it does that for them I have this but don't know how to get it to look at what's typed in the cell. Is it possible to do this?

Sub bllookup()
'
' bllookup Macro
'

'
    Range("B1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Description").PivotFilters. _
        Add Type:=xlCaptionContains, Value1:=("B1")
    Range("F1").Select
End Sub

 

[ndsutherland]

(Untested) Try:

Sub bllookup()
'
' bllookup Macro
'

'
Dim FilterVal as String
FilterVal = Range("B1").value
    

    ActiveSheet.PivotTables("PivotTable2").PivotFields("Description").PivotFilters. _
        Add Type:=xlCaptionContains, Value1:=FilterVal
    Range("F1").Select
End Sub

 

[jcaptchaos2]

I found this and have changed it to match my data but it stops on Field.Currentpage = NewCat

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
'This line stops the worksheet updating on every change, it only updates when cell
'B1 or B2 is touched
If Intersect(Target, Range("b1:b2")) Is Nothing Then Exit Sub
 
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim Newcat As String
 
'Here you amend to suit your data
Set pt = Worksheets("By Month").PivotTables("PivotTable2")
Set Field = pt.PivotFields("Description")
Newcat = Worksheets("By Month").Range("B1").Value
 
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = Newcat
pt.RefreshTable
End With
 
End Sub

 

[jcaptchaos2]

Thanks ndsutherland that worked.