Non-Active Sheet Autofilter

[trevortownsend]

Hi all,

On Sheet2, I have a drop down that changes the work week to display that week's bookings. On Sheet4, I have an "Override Tab" where the users can input their weekly forecast and any corrections to the bookings. What I'm trying to do is get a VBA autofilter on Sheet4 that activates rows with the word "hide" to hide when a new week is selected on Sheet2. I have that filter preselected to remove "hide", just need it to auto-update so that they don't have to select the filter every time.

Here's an example of the drop down on Sheet2.
[TABLE="width: 200"]
<tbody>[TR]
[TD]Week[/TD]
[TD]Quarter[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Q1[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

When the user selects Week 5 on Sheet2, I would like Sheet4 to autofilter cells I've already pre-filtered with a formula to show the word "Hide" for each row that isn't the current selected week.
[TABLE="width: 200"]
<tbody>[TR]
[TD]Hide[/TD]
[TD]Week 1
[/TD]
[/TR]
[TR]
[TD]Hide
[/TD]
[TD]Week 2[/TD]
[/TR]
[TR]
[TD]Hide[/TD]
[TD]Week 3[/TD]
[/TR]
[TR]
[TD]Hide[/TD]
[TD]Week 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Week 5[/TD]
[/TR]
[TR]
[TD]Hide[/TD]
[TD]Week 6[/TD]
[/TR]
</tbody>[/TABLE]

Right now I've been using the below, which works, but only after the user makes an edit to Sheet4 (I assume because it then becomes the ActiveSheet). How do I get it to auto-update without the user having to make Sheet4 active?

Private Sub Worksheet_Change(ByVal Target As Range)


ActiveSheet.Autofilter.ApplyFilter


End Sub

Thank you!

 

[bobsan42]

Not only for autofilter you should use a reference to the sheet:

Worksheets("Sheet4").Autofilter.ApplyFilter

However - you will have to find a way to trigger this if there is no change to a cell in sheet4.
It can be called by another Worksheet_Change event or you can use Workbook_SheetChange