Hide all worksheets that don't contain the string value of the active cell.

[USAMax]

I want two functions, the first will hide every worksheet that does not contain the string value of the current cell. I have a workbook with a list of properties and column 'L' has the address. That address will be a part of the name of at least two other worksheets Comps - ADDRESS and Prop Card - ADDRESS. Only the Main Property Sheet and those two worksheets should be visible.

The second function should unhide all worksheets.

I am sure this will be a loop that will go through every worksheet but I have not done this before.

Thanks,

Dave

 

[USAMax]

I am sorry, this was not as hard as I thought it would be.

Sub HideAllWorksheetsWithoutAddress()

    Dim WS_Count As Integer
    Dim I As Integer
    Dim strAddress As String
   
    'GET ADDRESS
    strAddress = Cells(ActiveCell.Row, 12)
    If strAddress = "" Or ActiveCell.Row = 1 Then GoTo CleanExit
   
    WS_Count = ActiveWorkbook.Worksheets.count

    For I = 1 To WS_Count
        If Sheets(I).Name <> "Main Property Sheet" Then
            If InStr(1, UCase(Sheets(I).Name), UCase(strAddress)) = 0 Then
                Sheets(I).Visible = False
            Else
                Sheets(I).Visible = True
            End If
        End If
    Next I

CleanExit:
End Sub


Sub UnhideAllWorksheets()
    Dim WS_Count As Integer
    Dim I As Integer
   
    WS_Count = ActiveWorkbook.Worksheets.count

    For I = 1 To WS_Count
        Sheets(I).Visible = True
    Next I

CleanExit:
End Sub

 

[Peter_SSs]

Thanks for posting your solution. However, for the future, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.