Hiding Columns with Dynamic/Changing Tab Names

[nutrastat]

PROBLEM: We need to hide several columns, on several sheets, based on a cell saying either TRUE or FALSE. But this is not so easy when the tab names (i.e. sheet names) can change, meaning that the formula for hiding these columns must be 'dynamic' also.

In the spreadsheet we have formulas calling on data from different sheets. Unfortunately, as these sheet names (i.e. tab names) can change e.g. going from 'Accommodation' to 'HouseKeeping', we have made the formulas indirected so that the formulas still work after such name changes.

We have one cell [Named: WK5_TRUE_FALSE] that changes to TRUE when there is a 5-week month and FALSE when there is a 4-week month.

We therefore need, when WK5_TRUE_FALSE = FALSE, to hide columns W,X, Y & Z on sheets Accommodation, Bars, Garbage & Galley, bearing in mind that Accommodation could be renamed Housekeeping.

Is this possible?

Any help you can give would be much appreciated as this is definately outside of my experience/capabilities.

 

[ColinKJ]

Hi nutrastat,

Which Cell on which sheet changes to True?

ColinKJ

 

[PCL]

Assuming there is somewhere a cell name: WK5_TRUE_FALSE
In the sheet's code where this cell is placed installed next code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Address = Range("WK5_TRUE_FALSE").Address) Then HIDE_COLUMN (Target.Value)
End Sub

in a module placed next code

Option Explicit
Sub HIDE_COLUMN(FLAG_COL As Boolean)
Dim SH As Object
    For Each SH In Sheets
    Select Case SH.Name
            Case "Accommodation", "Housekeeping", "Bars", "Garbage", "Galley"
                Sheets(SH.Name).Columns("W:Z").EntireColumn.Hidden = FLAG_COL
        End Select
    Next SH
End Sub