Excel: (ByVal Target As Range) Not Working

Afternoon, I am trying to get the filter on a pivot to change to the wholesaler selected from a drop down menu on another sheet. (There are some slight format changes between the list of wholesalers the selection is made from and their labelling in the raw data which the pivot accesses, hence the lookup). The code below works when ran manually but I wanted to automate the process by using (ByVal Target As Range). I think I understand where it is going wrong but I'm not sure which changes I need to make to get it working. Any help would be greatly appreciated! Sub PivotChange(ByVal Target As Range) Table1 = Sheets("Acrynyms").Range("D2:F24") On Error Resume Next Result = Application.WorksheetFunction.VLookup(Sheets("Returns Note").Range("B8"), Table1, 3, False) If Err <> 0 Then Result = xlErrNA End If If Not Application.Intersect(Target, Sheets("Returns Note").Range("B8")) Is Nothing Then Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name"). _ ClearAllFilters Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").CurrentPage _ = Result End If End Sub

This question generated 26 answers. To proceed to the answers, click here.

This thread is current as of May 14, 2014.

For more resources for Microsoft Excel