Hide columns with variable sheet names

[nutrastat]

Hi,

I have tried to put together some VBA code to hide columns on several sheets, using answers based on various responses but have difficulty because my sheet names may vary, as some ships call Accommodation - House Keeping, and Garbage - Incinerator.

To cope with these name requirements, on a sheet called 'Notes', column/row D152 to D156, I have code that extracts and gives me the sheet names. In these cells I have sheet names Accommodation, Stores, Restaurant, Bars & Garbage. Each cell is named after the sheet such as 'Tab_Name_Accommodation, Tab_Name_Stores, Tab_Name_Restaurant, etc. In this way, for headings within a sheet when the tab name is changed it also changes headings on the sheet, as I simply call up to 'Tab_Name_' cell reference, as everyting is based on the tab name.

My requirement: on some months there are 4 weeks and occasionally 5 weeks in a month, and I wish to hide Week 5 columns (AI to AL) on EACH of these sheets when the cell name WK05_TRUE_FALSE = FALSE (i.e. there is no week 5).

Can this be done by using the cell names (D152 to D156) rather than static names as I have seen in answers in the past?

 

[foverman]

on a sheet called 'Notes' ... Each cell is named after the sheet such as 'Tab_Name_Accommodation, Tab_Name_Stores, Tab_Name_Restaurant...

... I wish to hide Week 5 columns (AI to AL) on EACH of these sheets when the cell name WK05_TRUE_FALSE = FALSE ...

Can this be done by using the cell names (D152 to D156) rather than static names as I have seen in answers in the past?

So the cell names, D152- are named 'Tab_Name_Accomodation, etc? Correct?

If so, then try this:

Sub Hide5thWeek()
    'set to false manually for this test
    WK05_TRUE_FALSE = False
    If WK05_TRUE_FALSE = False Then
        strAccommodationSheet = Range("Tab_Name_Accommodation")
        strStoresSheet = Range("Tab_Name_Stores")
        strRestaurant = Range("Tab_Name_Restaurant")
        Worksheets(strAccommodationSheet).Columns("AI:AL").EntireColumn.Hidden = True
        Worksheets(strStoresSheet).Columns("F:I").EntireColumn.Hidden = True
        Worksheets(strRestaurant).Columns("F:I").EntireColumn.Hidden = True
    End If
End Sub