Macro to look up changed target on another sheet within same workbook

[Hare]

Hi, below I have a macro that runs from within the one sheet which looks at a data validation list selection and does the autofilter.

However I have now expanded my workbook and want all data entry to be done on a different worksheet while the sheet that currrently holds this macro show only the summary.

Put simply, I want to do the data validation selection in the "Data Entry" tab and have the filter work on the "Summary" tab. How do I edit this macro to read target on "Data Entry" tab?

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("BusinessArea")) Is Nothing Then Exit Sub
On Error Resume Next
ActiveSheet.ShowAllData
Range("B20:H883").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criteria"), Unique:=False
End Sub

Thanks,

Hare

 

[Hare]

Hi just wondering if anyone does have an idea on how to fix this.
Pls let me know.

 

[lenze]

Untested, but this should work. with the code in the Data Entry module

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("BusinessArea")) Is Nothing Then Exit Sub
On Error Resume Next
Dim ws As Worksheet
Set ws = Sheets("Summary")
ws.ShowAllData
ws.Range("B20:H883").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
ws.Range("Criteria"), Unique:=False
End Sub

 

[Hare]

Thanks Lenze It did work!