VBA Worksheet visibility when equal to cell range

[BrainDiseasee]

Hello,

I am working with a large workbook where a roster of people have a worksheet for every name. The goal is that i can divide this roster up in to teams or different ranges for each team. When a macro is activated it only shows the ws names that are equal the specified range in the selected macro. I have done something similar to this to create WS based on a a range of cells but i keep getting a mismatch error. Below is just a sample of the code i am trying to work with. Once again the goal is when the macro is activated to change the sheet visibilities to TRUE for all sheet names found in the range N2.END(xlDown)

Thanks in advance to anyone that can help.

Sub sheet_Vis()

Dim NameField As Range
Dim Mgr As Range

Dim WSheet As Worksheet
Dim WSheetFound As Boolean
Dim DataWSheet As Worksheet


Set DataWSheet = Worksheets("Data")
Set NameField = DataWSheet.Range("N2", DataWSheet.Range("N2").End(xlDown))

Application.ScreenUpdating = False

For Each Mgr In NameField

For Each WSheet In ThisWorkbook.Worksheets
If WSheet.Name = NameField Then
WSheetFound = True
WSheet.Visible = True
Exit For
Else
WSheetFound = False
WSheet.Visible = False

Application.ScreenUpdating = True

End If

Next WSheet

Next


End Sub

 

[BrainDiseasee]

While i think i have solved the previous issue i am now working on the opposite problem If i have multiple ranges i want to use to control the visibility too i need to make the visibility FALSE for the sheets not equal to my range. Below is the current code i used to control what sheet names are visible when the sheet name is in the range. Needing to make visibility false for all other sheets not equal to the range.


(PS i think this is sloppy and if there is a better way please let me know)

Sub Sheet_Vis()

Dim cell As Excel.Range
Dim sh As Worksheet

For Each cell In Worksheets("Data").Range("N2:N" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)

For Each sh In Worksheets
If sh.Name = cell.Value Then
sh.Visible = True

End If

Next
Next
End Sub

 

[Skyybot]

Test this on a copy of your Workbook as unexpected results may occur. Place this code in ThisWorkbook of your VBA Project Model.

Sub Sheet_Vis()

Dim cell As Range, sht As Worksheet, rng As Range, lRow As Long, ws As Worksheet, wb As Workbook
Set wb = ThisWorkbook: Set sht = wb.Sheets("Data")
lRow = sht.Columns("N").Rows(sht.Rows.Count).End(xlUp).Row
Set rng = sht.Range("N2:N" & lRow)
Application.ScreenUpdating = False
For Each ws In wb.Sheets
    ws.Visible = xlSheetHidden
        For Each cell In rng
            If ws.Name = cell.Value Then
                ws.Visible = xlSheetVisible
                Exit For
            End If
        Next cell
Next ws
Application.ScreenUpdating = True
End Sub

 

[BrainDiseasee]

Test this on a copy of your Workbook as unexpected results may occur. Place this code in ThisWorkbook of your VBA Project Model.

Sub Sheet_Vis()

Dim cell As Range, sht As Worksheet, rng As Range, lRow As Long, ws As Worksheet, wb As Workbook
Set wb = ThisWorkbook: Set sht = wb.Sheets("Data")
lRow = sht.Columns("N").Rows(sht.Rows.Count).End(xlUp).Row
Set rng = sht.Range("N2:N" & lRow)
Application.ScreenUpdating = False
For Each ws In wb.Sheets
    ws.Visible = xlSheetHidden
        For Each cell In rng
            If ws.Name = cell.Value Then
                ws.Visible = xlSheetVisible
                Exit For
            End If
        Next cell
Next ws
Application.ScreenUpdating = True
End Sub

This works great! Except i have realized i have not made this dynamic enough. How could i make this a run every time the cell value of N1 Changes?

Thanks in advance!

 

[Skyybot]

Place this in the "Data" Sheet of you VBA Project Model.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Me.Range("N1") Then
    ThisWorkbook.Sheet_Vis
End If
End Sub

 

[BrainDiseasee]

Place this in the "Data" Sheet of you VBA Project Model.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Me.Range("N1") Then
    ThisWorkbook.Sheet_Vis
End If
End Sub

Thanks again, i actually got that one figured out but i appreciate your help!

 

[Peter_SSs]

This works great!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.