Expand and collapse menu via filter on separate sheets

[mrsumadinac]

Hi All

This is my first post so hopefully I can give you all the info that you need to assist.

I currently have a workbook with four different sheets. The main sheet (Sheet 1) where all the information is gathered and populated to the three others (sheet 2-4)that have slightly different formatting. Each of the four sheets have a menu that expands and contracts based on a logical result (true/false). The menu is about 100 rows long and hides rows based on the true/false return of a formula. Meaning the logic that returns true/false (istext) in column K checks (istext) of column A and then automatically filters based on VBA
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.AutoFilter.ApplyFilter
End Sub

Although each sheet has the above code I find that the auto filter works fine when you enter a value in a cell and then hit enter. It doesn't necessarily do it for the other sheets that copy off the main sheet unless (as an example) you view a formula and hit enter.

To try and explain this better the steps taken to fill out the main sheet (sheet 1) are below.

Variable in Cell A1 and B1 return results of a list that populate from A2 down to A100. The variable in cells A1 and B1 can have results from 1 line item up to 100. A 'helper' column (let's call it Column C - which the user cant see) checks the results in column A2 onward via istext() then the filter function being set to true filters column A based on the istext forumla in column C.

Now - while the rows expand and contract based on column C output in the mainsheet, (Sheet 1) and even though the three other worksheets (Sheets 2-4) have the same formula(s) as the main sheet (sheet 1) only the main sheet auto filters. The other sheets (sheet 2-4) remain static until there is user input on that sheet (which needs to be avoided).

I have tried recording a macro that steps through all the sheets and refreshes the filter. (below) but nothing seems to be working - can someone help me out?

Sheets("Sheet 1").Select
ActiveSheet.Unprotect
Sheets("Sheet 1").Select
Sheets("Sheet 2").Visible = True
ActiveWindow.SmallScroll Down:=36
ActiveSheet.Range("$L$59:$L$108").AutoFilter Field:=1, Criteria1:="TRUE"
Sheets("Sheet 2").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet 1").Select
Sheets("Sheet 3").Visible = True
ActiveWindow.SmallScroll Down:=41
ActiveSheet.Range("$L$59:$L$108").AutoFilter Field:=1, Criteria1:="TRUE"
Sheets("Sheet 3").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet 1").Select
Sheets("Sheet 4").Visible = True
ActiveSheet.Range("$L$59:$L$108").AutoFilter Field:=1, Criteria1:="TRUE"
Sheets("Sheet 4").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet 1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[Alex Blakenburg]

I am unclear as to whether you need the filter applied to Sheet 1 or only the others.
In the below I have only applied it to the others. Add "Sheet 1" to the array if you want it included.
You don't need to make Visible to apply the filter.

See how you go.

Sub RefreshFilter()

    Dim ShtsToFltr As Variant
    Dim i As Long
    Sheets("Sheet 1").Select
    ActiveSheet.Unprotect
    
    ShtsToFltr = Array("Sheet 2", "Sheet 3", "Sheet 4")
    For i = 0 To UBound(ShtsToFltr)
        With Worksheets(ShtsToFltr(i))
            '.Visible = True        ' Making it visible is not necessary
            .Range("$L$59:$L$108").AutoFilter Field:=1, Criteria1:="TRUE"
            '.Visible = False       ' relates to above line
        End With
    Next i
    
    Sheets("Sheet 1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub