Run a Macro everytime a change is made to a worksheet

[brumby]

Hiya folks.

Is it possible to run a filter macro everytime a change is made to a specific worksheet?

I have a sheet called "Master Production Plan", which feeds a summary sheet called "PROD PLAN", I have a macro as below, which I would like to autorun each time I make a change to Master Production Plan

ActiveSheet.Range("$A$4:$AT$144").AutoFilter Field:=2, Criteria1:=Array("1" _
, "2", "3", "4", "5", "6", "7", "8", "9", "10"), Operator:=xlFilterValues

Can anyone help?

Many Thanks!!!!

 

[Domski]

Hi,

Which sheet should the autofilter be applied to?

Dom

 

[Fishboy]

Hiya folks.

Is it possible to run a filter macro everytime a change is made to a specific worksheet?

I have a sheet called "Master Production Plan", which feeds a summary sheet called "PROD PLAN", I have a macro as below, which I would like to autorun each time I make a change to Master Production Plan

ActiveSheet.Range("$A$4:$AT$144").AutoFilter Field:=2, Criteria1:=Array("1" _
, "2", "3", "4", "5", "6", "7", "8", "9", "10"), Operator:=xlFilterValues

Can anyone help?

Many Thanks!!!!

Hi,

Which sheet should the autofilter be applied to?

Dom

Judging by the existing code it is the active sheet, the same one with data being entered into it to run the macro.

Brumby, try adding the following code directly to the Master Production Plan sheet by right-clicking the sheet tab and selecting View Code. Obviously you will need to change YOURFILTERMACRO to whatever you have called your existing code. You can also tweak the range A1:Z100 to suit your data.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:Z100")) Is Nothing Then Exit Sub Else
Call YOURFILTERMACRO
End Sub

 

[brumby]

Hi There........thanks for the advice, its as below

I enter the data into "Master Production Plan"

The Macro to filter is in "PROD PLAN" worksheet,

sorry for the confusion

appreciate your help guys!!!

 

[Fishboy]

Hi There........thanks for the advice, its as below

I enter the data into "Master Production Plan"

The Macro to filter is in "PROD PLAN" worksheet,

sorry for the confusion

appreciate your help guys!!!

If the filter isn't going to be happening on the Master Production Plan sheet then you will need to recode your filter macro because of this bit:

ActiveSheet.Range("$A$4:$AT$144").AutoFilter Field:=2, Criteria1:=Array("1" _
, "2", "3", "4", "5", "6", "7", "8", "9", "10"), Operator:=xlFilterValues

If you change this line in your filter macro to this...:

Worksheets("PROD PLAN").Range("$A$4:$AT$144").AutoFilter Field:=2, Criteria1:=Array("1" _
, "2", "3", "4", "5", "6", "7", "8", "9", "10"), Operator:=xlFilterValues

...then Excel will not try to apply the filter to Master Production Plan, which would be the ActiveSheet.

 

[brumby]

SUPERB!!!! thanks guys, works a treat

 

[Fishboy]

SUPERB!!!! thanks guys, works a treat

Happy to help, glad we got there in the end. Feel free to click the like button on the posts with answers to help anyone else with a similar query find the solutions faster.