Macro to filter a pivot table by selected cell

[MPBJR]

Hi,
I have a Pivot table summary in worksheet sheet1 with a list of customer names in column "A" with sales by month in columns b-f. There is a filter in the pivot table by customer name in column "A".
Worksheet sheet2 has a list of detailed orders with the customer name in column "B".
What I'd like to do is simply select a customer name cell in worksheet 2 , and when I click the macro button, go to worksheet sheet1 and have it filter the pivot table to the customer name I selected in worksheet2. The pivot table name is simply "CUSTOMERS".

I've searched the web and this site for something similar and can not find anything that works.
Any help would be much appreciated.

thank you

 

[DanteAmor]

Try this.
In this line of the macro write the name of the customer field.

With ActiveSheet.PivotTables("CUSTOMERS").PivotFields("Customer")

Sub filter_pivot_table()
  Dim sName As String, pItem As PivotItem, n As Long
  
  If ActiveCell.Value = "" Or ActiveCell.Column <> 2 Then
    MsgBox "Select a customer name"
    Exit Sub
  End If
  
  sName = ActiveCell.Value
  Sheets("Sheet1").Select
  With ActiveSheet.PivotTables("CUSTOMERS").PivotFields("Customer")
    .ClearAllFilters
    For Each pItem In .PivotItems
      If pItem <> sName Then
        n = n + 1
        If n < .PivotItems.Count Then
          pItem.Visible = False
        Else
          .ClearAllFilters
          MsgBox "The name does not exist"
        End If
      End If
    Next
  End With
End Sub

 

[MPBJR]

That works perfect! Thanks for the help, much appreciated!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.