Hide Columns Based on Cell CLick

[shebert61]

I need to hide columns across multiple sheets

Setup:
(Sheet 1) The filter list is in D16:DX (the list may vary in number) DX+1 value is "Show All"

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Fish[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Snake[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Rat[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Show All[/TD]
[/TR]
</tbody>[/TABLE]


OR

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Snake[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Show All[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Action:
Target Sheets (Sheet3-Sheetx except sheet14 the number of sheets may vary but will be at least 4); if it can be done easily, I dont mind changing sheet14 to sheet3, This would probably make more sense but I'm not sure how to do that. Then the action would apply to sheet 4-sheetx

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Cat[/TD]
[TD]Fish[/TD]
[TD]Snake[/TD]
[TD]Rat[/TD]
[TD]Comments[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On click of a cell in the Filter list, on all sheets in workbook (the number of sheets may vary from one workbook to the next) except 1,2, and 14 in column M through ColumnX (Number varies based on "Filter List") hide columns except what is selected in sheet1 D4 (filter criterion)
So in example:
Sheet 1 click on Snake
All sheets except 1,2 and 14 or sheet4-sheetx

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"] S [/TD]
[/TR]
[TR]
[TD]Snake[/TD]
[TD]Comment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Clicking on Show All or Closing the Workbook removes the filter as well.

Thanks in advance and apologies as this is the first post and attempt at coding in a really long time.

 

[tonyyy]

shebert61,

Welcome to the Board.

On click of a cell in the Filter list...

...except what is selected in sheet1 D4 (filter criterion)

These two phrases seem to conflict so I arbitrarily chose to go with the first action.

The code should be placed in the Sheet module that corresponds to the sheet with the filter list.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim rng As Range, found As Range, found2 As Range
Dim crit As String
Dim LastCol As Long, n As Long, i As Long

Set rng = Sheets("Sheet1").Range("D16", Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp))
If Not Intersect(Target, rng) Is Nothing Then
    crit = Target.Value
    For n = 3 To Sheets.Count
        Set ws = Sheets(n)
        If ws.Name <> "Sheet14" Then
            If crit = "Show All" Then
                ws.Columns.Hidden = False
            Else
                Set found = ws.Rows(1).Find(What:=crit, After:=Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
                Set found2 = ws.Rows(1).Find(What:="Comments", After:=Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
                If Not found Is Nothing And Not found2 Is Nothing Then
                    LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                    For i = 13 To LastCol
                        ws.Columns(i).Hidden = True
                    Next i
                    ws.Columns(found.Column).Hidden = False
                    ws.Columns(found2.Column).Hidden = False
                End If
            End If
        End If
    Next n
End If
End Sub

Cheers,

tonyyy