Hide Worksheet based on cell without using worksheet name in VBA

[wannaBvba]

Hello! I have used this site for answers for several years so far. This is my very first post in Mr. Excel primarily because my previous search of the forum has always answered my questions! I am very impressed with the skills of the people posting answers/recommendations on this site. I apologize in advance if I post incorrectly. I have read and searched the threads for this particular challenge with no success.

Problem: I have a worksheet report that comes out of a financial system with approximately 200 worksheet. I run a change worksheet based on cell name macro to change the default name of each worksheet. In addition, I need to hide some of the worksheets based on a value in the cell (D11) of each worksheet "Active" vs "Inactive" -- hide if D11 = "Inactive"

I have found several ways to hide worksheets based on cell value, but they require the name of the worksheet be listed in the formula. Help?

 

[mumps]

Can you post the macro you are currently using?

 

[wannaBvba]

Can you post the macro you are currently using?

Hi. The "change name" macro is listed below. I have not sorted out a macro that will work for the "hide" function yet.


Rename Tabs
Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("B11")) > 0 Then
ws.Name = ws.Range("B11").Value
End If
On Error GoTo 0
If ws.Name <> ws.Range("B11").Value Then
MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub

 

[mumps]

Try:

Sub tabname()
    Dim ws As Worksheet
    For Each ws In Worksheets
        On Error Resume Next
        If Len(ws.Range("B11")) > 0 Then
            ws.Name = ws.Range("B11").Value
        End If
        On Error GoTo 0
        If ws.Name <> ws.Range("B11").Value Then
            MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
        End If
        If Range("D11") = "Inactive" Then
            ws.Visible = False
        Else
            ws.Visible = True
        End If
    Next ws
End Sub

 

[wannaBvba]

It worked perfectly! Thank you so much for the help!

 

[mumps]

You are very welcome.