Excel VB to hide an entire row based on sheet name match and cell dynamically.

[spittingfire]

Hi All,

I have attached a sample workbook.

What I'm looking to do in the sample workbook is I have 3 sheets. One sheet is Sales SOD, one is Sales and the other is Sales Bilingual.

What I want to do in the Sales SOD sheet I will like to hide a row dynamically if certain conditions are matched.

Each name in Sales SOD will have a matching sheet with the same name.

So for Sales Bilingual in the Sales SOD sheet I want to hide that row if cell AE56 in the Sales Bilingual sheet = 0

Since I have many SODs with many sheets I will like to do this dynamically. All matches will however be based on cell AE56 of the individual sheet name.

Any assistance would be appreciated.

[Paul_Hossler]

Try this in the 'Sales' worksheet code module

Option Explicit

Private Sub Worksheet_Activate()
    Dim rSheetNames As Range, rSheetName As Range
    
    Application.ScreenUpdating = False
    
    With Me
        Range(.Cells(6, 1), .Cells(.Rows.Count, 1)).Rows.Hidden = False
        Set rSheetNames = Range(.Cells(7, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    For Each rSheetName In rSheetNames.Cells
        On Error GoTo NextSheet
        If Worksheets(rSheetName.Value).Range("AE56").Value = 0 Then
            Me.Rows(rSheetName.Row).Hidden = True
        End If
    
NextSheet:
    Next
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

and this in the ThisWorkbook code module

Option Explicit

Private Sub Workbook_Open()
    Worksheets("Sales SOD").Select
End Sub

[spittingfire]

Thanks Paul,

That is awesome and worked well. Much appreciated.

[Paul_Hossler]

Glad

BTW, I made a error in the instructions (Worksheet_Activate should be in the Sales SOD code module, not Sales)

The attachment was right, so I hope you used it as an example

[spittingfire]

Thanks for the info and yes I did use the example from the attachment.