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
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