VBA to hide sheets based on dynamic cell values

[RoryColtman]

Hi - relatively new to VBA and needassistance!
I have a list of around 50 cell values that sum up columns in correspondingsheets in the same workbook, I would like to hide the particular sheet based onthe value of the cell.
In the example below I would like to hide sheet 1 only, but should the valuedrop to below 0 in sheet 2 I would like to hide sheet 2 as well.

Sheet name Sum of column A in sheet
Sheet1 -1000
Sheet2 500
Sheet3 300
Sheet4 600

I hope this makes sense! Tks

 

[Yongle]

Try this
- code below loop all sheets in workbook, finds sheet name in column A and uses value in column C to determine if visible \ hidden
- On Error Resume Next prevents code failing if sheet name is not in list etc

Sheet Name = "Index"
Column A : List of Sheet Names
Column B : values associated with sheet
Column C : Different formula in each cell returning True\False to tell VBA if sheet should be visible\hidden

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Sheet Name[/td][td]Value[/td][td]Visible ?[/td][td] Formula in column C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Sheet1[/td][td]

1000​

[/td][td]

FALSE​

[/td][td] =B2>1000[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Sheet2[/td][td]

500​

[/td][td]

TRUE​

[/td][td] =B3>=0[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Sheet3[/td][td]

300​

[/td][td]

FALSE​

[/td][td] =B4<200[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Sheet4[/td][td]

600​

[/td][td]

TRUE​

[/td][td] =B5<=1000[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Index[/td][/tr][/table]

Amend to match your requirements

Sub HideSheetsBasedOnRules()
    Dim ws As Worksheet, rng As Range
    With Sheets("[COLOR=#ff0000]Index[/COLOR]")
        Set rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With
        
    On Error Resume Next
    For Each ws In ThisWorkbook.Sheets
        ws.Visible = rng.Find(ws.Name).Offset(, 2).Value
    Next
End Sub

 

[DanteAmor]

I guess the names start in cell A2.
Run this macro on the sheet where you have the sheet-sum relationship

Sub test()
    Dim c As Range
    On Error Resume Next
    For Each c In Range("[COLOR=#0000ff]A2[/COLOR]", Range("A" & Rows.Count).End(xlUp))
        If c.Offset(0, 1) < 0 Then Sheets(c.Value).Visible = 0
    Next
End Sub