Run a Macro when cell is changed

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
145
Office Version
  1. 365
Platform
  1. Windows
Good Day to you all

I have a dilema

I have this code:
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]:

Code:
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
 
Last edited:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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.
 
Upvote 0
Please also supply links to the other sites where you have asked the same question.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top