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.

Week	Quarter	Year
5	Q1	20

<tbody>
</tbody>

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.

Hide	Week 1
Hide	Week 2
Hide	Week 3
Hide	Week 4
	Week 5
Hide	Week 6

<tbody>
</tbody>

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