none
How can I loop through sheets and flag certain sheets for hiding and then hide all if the flag is set to true? RRS feed

  • Question

  • I'm playing with the code below, and I could tell almost immediately it wouldn't work.  The problem is that I loop through 4 ranges and test cell values to see if they are 'Yes' or 'No'.  If 'Yes' I don't want to hide the sheet and if 'No' I want to hide the sheet.

    Here's the problem.  If I have a single 'Yes' in Range("O3:P14") I DON'T want to hide the sheets that start with a certain word, like Credit, and if I have ALL 'No' values, I DO want to hide sheets that start with a certain word.  When I get to the Range("O18:P31") I have all 'No' values but the sheets that start with the word 'Market' should be hidden but not sheets that start with the word 'Credit'. 

    I think I need to test for 'Yes' and 'No' values, sat a flag to hide a sheet or not hide a sheet, and then do all the hiding at the end.  How can I do that?

    Sub HideSheets()
    
    Dim MyCell As Range
    Dim MyRangeCredit As Range
    Dim MyRangeMarket As Range
    Dim MyRangeLiquidity As Range
    Dim MyRangeStrategic As Range
    Dim ws As Worksheet
    
        ' Look at Credit range
        Set MyRangeCredit = Sheets("Summary").Range("O3:P17")
        For Each MyCell In MyRangeCredit
            If MyCell.Value <> "Yes" Or MyCell.Offset(0, 1).Value <> "Yes" Then
                For Each ws In Sheets
                    If Not ws.Name Like "Credit" & "*" And Not ws.Name = "Summary" And Not ws.Name = "Table of Contents" Then
                        ws.Visible = False
                    End If
                Next
            End If
        Next MyCell
        
        ' Look at Market range
        Set MyRangeMarket = Sheets("Summary").Range("O18:P31")
        For Each MyCell In MyRangeMarket
            If MyCell.Value <> "Yes" Or MyCell.Offset(0, 1).Value <> "Yes" Then
                For Each ws In Sheets
                    If Not ws.Name Like "Market" & "*" And Not ws.Name = "Summary" And Not ws.Name = "Table of Contents" Then
                        ws.Visible = False
                    End If
                Next
            End If
        Next MyCell
        
        ' Look at Liquidity range
        Set MyRangeLiquidity = Sheets("Summary").Range("O32:P38")
        For Each MyCell In MyRangeLiquidity
            If MyCell.Value <> "Yes" Or MyCell.Offset(0, 1).Value <> "Yes" Then
                For Each ws In Sheets
                    If Not ws.Name Like "Liquidity" & "*" And Not ws.Name = "Summary" And Not ws.Name = "Table of Contents" Then
                        ws.Visible = False
                    End If
                Next
            End If
        Next MyCell
        
        ' Look at Strategic range
        Set MyRangeStrategic = Sheets("Summary").Range("O39:P50")
        For Each MyCell In MyRangeStrategic
            If MyCell.Value <> "Yes" Or MyCell.Offset(0, 1).Value <> "Yes" Then
                For Each ws In Sheets
                    If Not ws.Name Like "Strategic" & "*" And Not ws.Name = "Summary" And Not ws.Name = "Table of Contents" Then
                        ws.Visible = False
                    End If
                Next
            End If
        Next MyCell
        
        MsgBox ("Done hiding sheets!!")
        
    End Sub



    MY BOOK

    Tuesday, April 5, 2016 9:44 PM

Answers

  • I got it working by creating a sheet called 'AllSheets' and writing all sheet names in ColumnA, if the conditions were met, and then reading the cells in that array to hide the appropriate sheets.

    Sub HideSheets()
    
    Dim MyCell As Range
    Dim MyRangeCredit As Range
    Dim MyRangeMarket As Range
    Dim MyRangeLiquidity As Range
    Dim MyRangeStrategic As Range
    Dim ws As Worksheet
    Dim flg As Boolean
    Dim lr As Long
    
    Sheets("AllSheets").Select
    Cells.Select
    Selection.ClearContents
        
        Call ShowAllSheets
        
        ' Look at Credit range
        If Application.CountIf(Sheets("Summary").Range("O3:P17"), "*Yes*") = 0 Then
            For Each ws In Sheets
                If ws.Name Like "Credit" & "*" & "*" Then
                    lr = Sheets("AllSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("AllSheets").Range("A" & lr).Value = ws.Name
                End If
            Next
        End If
    
        ' Look at Market range
        If Application.CountIf(Sheets("Summary").Range("O18:P31"), "*Yes*") = 0 Then
            For Each ws In Sheets
                If ws.Name Like "Market" & "*" Then
                    lr = Sheets("AllSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("AllSheets").Range("A" & lr).Value = ws.Name
                End If
            Next
        End If
        
        ' Look at Liquidity range
        If Application.CountIf(Sheets("Summary").Range("O32:P38"), "*Yes*") = 0 Then
            For Each ws In Sheets
                If ws.Name Like "Liquidity" & "*" Then
                    lr = Sheets("AllSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("AllSheets").Range("A" & lr).Value = ws.Name
                End If
            Next
        End If
        
        ' Look at Strategic range
        If Application.CountIf(Sheets("Summary").Range("O39:P50"), "*Yes*") = 0 Then
            For Each ws In Sheets
                If ws.Name Like "Strategic" & "*" Then
                    lr = Sheets("AllSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("AllSheets").Range("A" & lr).Value = ws.Name
                End If
            Next
        End If
    
        Call NowHideSheets
         
        MsgBox ("Done hiding sheets!!")
        
    End Sub
    
    Sub NowHideSheets()
    
    Dim MyRange1 As Range
        
        Set MyRange1 = Sheets("AllSheets").Range("A2")
        Set MyRange1 = Range(MyRange1, MyRange1.End(xlDown))
        
        On Error Resume Next
            For Each MyCell In MyRange1
                MySheet = MyCell
                Worksheets(MySheet).Visible = False
            Next MyCell
    
    End Sub


    MY BOOK

    • Proposed as answer by David_JunFeng Thursday, April 7, 2016 2:16 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:23 PM
    Wednesday, April 6, 2016 3:25 AM

All replies

  • I got it working by creating a sheet called 'AllSheets' and writing all sheet names in ColumnA, if the conditions were met, and then reading the cells in that array to hide the appropriate sheets.

    Sub HideSheets()
    
    Dim MyCell As Range
    Dim MyRangeCredit As Range
    Dim MyRangeMarket As Range
    Dim MyRangeLiquidity As Range
    Dim MyRangeStrategic As Range
    Dim ws As Worksheet
    Dim flg As Boolean
    Dim lr As Long
    
    Sheets("AllSheets").Select
    Cells.Select
    Selection.ClearContents
        
        Call ShowAllSheets
        
        ' Look at Credit range
        If Application.CountIf(Sheets("Summary").Range("O3:P17"), "*Yes*") = 0 Then
            For Each ws In Sheets
                If ws.Name Like "Credit" & "*" & "*" Then
                    lr = Sheets("AllSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("AllSheets").Range("A" & lr).Value = ws.Name
                End If
            Next
        End If
    
        ' Look at Market range
        If Application.CountIf(Sheets("Summary").Range("O18:P31"), "*Yes*") = 0 Then
            For Each ws In Sheets
                If ws.Name Like "Market" & "*" Then
                    lr = Sheets("AllSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("AllSheets").Range("A" & lr).Value = ws.Name
                End If
            Next
        End If
        
        ' Look at Liquidity range
        If Application.CountIf(Sheets("Summary").Range("O32:P38"), "*Yes*") = 0 Then
            For Each ws In Sheets
                If ws.Name Like "Liquidity" & "*" Then
                    lr = Sheets("AllSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("AllSheets").Range("A" & lr).Value = ws.Name
                End If
            Next
        End If
        
        ' Look at Strategic range
        If Application.CountIf(Sheets("Summary").Range("O39:P50"), "*Yes*") = 0 Then
            For Each ws In Sheets
                If ws.Name Like "Strategic" & "*" Then
                    lr = Sheets("AllSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("AllSheets").Range("A" & lr).Value = ws.Name
                End If
            Next
        End If
    
        Call NowHideSheets
         
        MsgBox ("Done hiding sheets!!")
        
    End Sub
    
    Sub NowHideSheets()
    
    Dim MyRange1 As Range
        
        Set MyRange1 = Sheets("AllSheets").Range("A2")
        Set MyRange1 = Range(MyRange1, MyRange1.End(xlDown))
        
        On Error Resume Next
            For Each MyCell In MyRange1
                MySheet = MyCell
                Worksheets(MySheet).Visible = False
            Next MyCell
    
    End Sub


    MY BOOK

    • Proposed as answer by David_JunFeng Thursday, April 7, 2016 2:16 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:23 PM
    Wednesday, April 6, 2016 3:25 AM
  • Congratulation, thanks for you sharing your solution, that will help community member when they have same issue.
    Wednesday, April 6, 2016 6:41 AM