(ByVal Target As Range) Not Working

[MB161]

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

 

[MB161]

No errors, just nothing happens??

 

[Joe4]

A few things.

You have not declared "Table1". If it is a range object (which it appears to be), you need to use the "Set" statement, like you did with "KeyCells".

"Target" is already a range object. So "Range(Target.Address)" is totally unnecessary. Just use "Target".

 

[MB161]

I have made the changes you suggested and feel like I'm getting close to the solution but the filter is still not updating. Any ideas what I'm missing?

Sub Worksheet_Change(ByVal Target As Range)


    Dim KeyCells As Range
    Dim Table1 As Range
    
    Set KeyCells = Sheets("Returns Note").Range("B8")
    Set 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(KeyCells, Target) Is Nothing Then
 Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name"). _
    ClearAllFilters
    
    Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").CurrentPage _
    = Result
    
 End If

 

[Norie]

What happens if you remove On Error Resume Next and re-arrange things a bit.

Sub Worksheet_Change(ByVal Target As Range)
Dim Result As Variant
Dim KeyCells As Range
Dim Table1 As Range
    
    Set KeyCells = Sheets("Returns Note").Range("B8")
    Set Table1 = Sheets("Acrynyms").Range("D2:F24")
 
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then

        Result = Application.VLookup(Target, Table1, 3, False)

        If IsError(Result) Then Exit Sub

        Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").ClearAllFilters
        Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").CurrentPage = Result

    End If

End Sub

By the way, where have you put the code?

 

[MB161]

I have written the code in "This Worksheet" part of the VBA editor. Was this the right place to do so?

I have left the office now and won't be able to let you know if your code works until tomorrow morning when I have the sheet in front of me again.

In the meantime, thanks for your help so far. I look forward to finding the solution!

 

[Norie]

There is no ThisWorksheet in the VBA editor, if you mean ThisWorkbook then that's not the correct place for the code.

It should go in a worksheet module.

 

[Joe4]

I have written the code in "This Worksheet" part of the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> editor.

I don't think any such object exists. There is a "ThisWorkbook" module, and sheet modules named like "Sheet1 (Sheet1)".
Worksheet event procedure code needs to be placed in the Sheet modules.

An easy way to get it in the correct place is to go to the sheet you want to apply it to in Excel, right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste your code in the VB Editor window that pops up.

 

[MB161]

I'd have to check that's where I put it in the morning but I guess that would explain why nothing is happening. I'll let you know. Thanks

 

[MB161]

This worked perfectly thanks although not when added as a module but when pasted into the correct sheet as an object via right clicking the the tab and selecting "View Code" as Joe4 said. I'm not sure I fully understand the difference between an object and a module but will read up on this.

A big thank you to you both for your help!

What happens if you remove On Error Resume Next and re-arrange things a bit.

Sub Worksheet_Change(ByVal Target As Range)
Dim Result As Variant
Dim KeyCells As Range
Dim Table1 As Range
    
    Set KeyCells = Sheets("Returns Note").Range("B8")
    Set Table1 = Sheets("Acrynyms").Range("D2:F24")
 
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then

        Result = Application.VLookup(Target, Table1, 3, False)

        If IsError(Result) Then Exit Sub

        Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").ClearAllFilters
        Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").CurrentPage = Result

    End If

End Sub

By the way, where have you put the code?

 

[Joe4]

You are not actually adding an object, but you are adding it to a pre-defined module (there are Workbook and Sheet modules). If you add Event Procedure VBA code to a pre-defined module, they are triggered automatically upon some event happening.

You are probably used to adding your VBA code to General VBA modules. That is most of the code that you run manually, or by a button or shortcut. Event Procedure VBA code cannot be added to a General Module (it won't run automatically).

Here is a good write-up on Event Procedures: Events In Excel VBA