Run a Macro when cell is changed

[tigerdel]

Good Day to you all

I have a dilema

I have this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Sheets("Sales").Range("All_Data").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), _
CopyToRange:=Range("Extract"), _
Unique:=False
End If
End Sub

It is in Sheet = Company and returns the required results

I also need to run the same code in Sheet = Broker and also Sheet = Party

I built the same code but changed it to meet the requirements of the Sheet [Broker]:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Sheets("Sales").Range("DATA").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Broker").Range("Criteria_Broker"), _
CopyToRange:=Sheets("Broker").Range("Extract_Broker"), _
Unique:=False
End If
End Sub

The named ranges are built but although I receive no errors, no data is populated in Sheet [Broker]

Any ideas because I am now completely frustrated

Thanks for looking

I can share the workbook if anyone wants it

 

[offthelip]

I suspect your problem is because you are trying to apply a filter to another worksheet using the worksheet changevent. I have found quite a lot of limitations of what one can do on another worksheet inside a worksheet change event.
What I suggest is moving your code to the Workbook "sheetchange" event handler, then you will have better chance of getting it working. you will need to modify it to detect the appropriate sheets

 

[Fluff]

Cross posted https://www.excelguru.ca/forums/sho...acro-when-a-cell-is-changed&p=41086#post41086

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

Please also supply links to the other sites where you have asked the same question.