Could someone please help me with this code

[Hutchington]

I am hoping that someone can help me. I have the below code that loops through every worksheet in my workbook to find instances of names and makes all of these worksheets visible. In the Range "B2:B300" is a list of all the tabs worksheets within the workbook. Each tab can dynamically be made visible by inputting a "Y" in the cells "C2:C300", and likewise hidden by inputting an "N".

Sub WS_Change()

Dim ws As Worksheet
Dim wkb As Workbook
Dim cel As Range
Dim cel2 As Range
Dim TN As Range
Dim TN2 As Range
Dim CDS As Object

Set wkb = ActiveWorkbook
Set ws = wkb.Worksheets(1)
Set CDS = Sheets("Control")

For Each cel In Range("F15:F100")
If UCase(cel.Value = "Y") Then
    For Each ws In Sheets 'This statement starts the loop
    For Each cel2 In ws.Range("A2:A100")
    If cel.Offset(0, -1).Value = cel2.Value Then
    ws.Visible = True
    Exit For
    End If
    Next cel2
    Next ws

    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    For Each TN In CDS.Range("B2:B300")
        If ws.Name = TN.Value Then
        TN.Offset(0, 1) = "Y"
    End If
    Next TN
    End If
    Next ws
End If
Next cel
Call CDS.Activate
End Sub�

What I want to do is make the above dynamic too so I am not relying on a macro. I have converted the above into a Case Statement, but I cannot get it to work. The idea is, if you put a "Y" in a field within the range F15:F100 it will make visible all worksheets with that have the name in the offset cell. If you put an "N" in all the cells within the range C2:C300 with that dynamic code, thereby closing all of the tabs. It would then run the code in WS_Change() to see if there are any other cells with a "Y" in them and thereby reopen all the tabs with names still selected. That's the plan, but I can't seem to get it to work. Would someone mind having a look at the below and point out where I am going wrong, please.

Sub WrkS_Change(ByVal Target As Range)

'Sub WS_Change()

Dim ws As Worksheet
Dim wkb As Workbook
Dim cel As Range
Dim cel2 As Range
Dim TN As Range
Dim TN2 As Range
Dim CDS As Object

Set wkb = ActiveWorkbook
Set ws = wkb.Worksheets(1)
Set CDS = Sheets("Control")

'On Error GoTo EH

If Application.Intersect(Target, CDS.Range(Cells(15, 6), Cells(100, 6))) Is Nothing Then Exit Sub
'Application.EnableEvents = False
Select Case UCase(Target.Value)
    Case "Y"
    For Each ws In Sheets
    For Each cel2 In ws.Range("A2:A100")
    If Cells(Target.Row, Target.Column - 1).Value = cel2.Value Then
    ws.Visible = True
    Exit For
    End If
    Next cel2
    Next ws

    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    For Each TN In CDS.Range("B2:B300")
        If ws.Name = TN.Value Then
        TN.Offset(0, 1) = "Y"
    End If
    Next TN
    End If
    Next ws

    Case "N"

    For Each TN2 In CDS.Range("C2:C239")
    TN2 = "N"
    Next TN2

    For Each cel In Range("F15:F100")
    If UCase(cel.Value = "Y") Then
    For Each ws In Sheets 'This statement starts the loop
    For Each cel2 In ws.Range("A2:A100")
    If cel.Offset(0, -1).Value = cel2.Value Then
    ws.Visible = True
    Exit For
    End If
    Next cel2
    Next ws

    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    For Each TN In CDS.Range("B2:B300")
        If ws.Name = TN.Value Then
        TN.Offset(0, 1) = "Y"
    End If
    Next TN
    End If
    Next ws
End If
Next cel

End Select

'EH:
'Application.EnableEvents = True
End Sub�

 

[offthelip]

I think you need to change this line:

For Each cel2 In ws.Range("A2:A100")

to

For Each cel2 In CDS.Range("A2:A100")

You need to do it in two places.
My only other comments is that the way you have coded this is over complicated and is very slow code because it doing multiple loops through multiple sheets every time you change anything on the worksheet. so I suspect you are going to notice that that worksheet ends up being frustratingly slow to use
It would be much much faster if you did it with variant arrays

 

[Hutchington]

I think you need to change this line:

For Each cel2 In ws.Range("A2:A100")

to

For Each cel2 In CDS.Range("A2:A100")

You need to do it in two places.
My only other comments is that the way you have coded this is over complicated and is very slow code because it doing multiple loops through multiple sheets every time you change anything on the worksheet. so I suspect you are going to notice that that worksheet ends up being frustratingly slow to use
It would be much much faster if you did it with variant arrays

Hi offthelip,
Thanks for the reply. I have checked the code with your change but it is still not working. The CDS refers to the control sheet. It is the only one that doesn't have the Names in that cell range. I would be very grateful if you could show me how I could set this up with variant arrays to speed the processing up.
Thanks

 

[offthelip]

I don't understand your second requirement at all, but the first one is very simple ( if I have understood it correctly) This code uses a variant array to load all the data in the control sheet, and it loops through them all and it change the visiblitly of the sheet only if the Y?N is different to the current visibility, so this will be very fast is suitable to put in the worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Control")
 inarr = .Range("B2:C300")
 For i = 1 To 299
  If inarr(i, 1) <> "" Then
    If inarr(i, 2) = "Y" And Not (Worksheets(inarr(i, 1)).Visible) Then
     Worksheets(inarr(i, 1)).Visible = True
    End If
    If inarr(i, 2) = "N" And (Worksheets(inarr(i, 1)).Visible) Then
     Worksheets(inarr(i, 1)).Visible = False
    End If
  End If
Next i
End With
End Sub

 

[offthelip]

I have been struggling to understand your requirements and I had a further thought and I was wondering whether you wanted to be able to control the visibility from any of the worksheets. So if you take the code I wrote in #4 above and put it in the "Control" worksheet code , put the list of the worksheet names in column B and Y/N in column C on the Control sheet.
and then put the following code in all the other worksheets. you will get the same layout with sheets names and Y/N on every worksheet and you can change it from any of the sheets. This works by copying the B2:C300 from the control sheet to the active sheet as you activate it and then copy the data back to the control sheet if you change it on the active sheet. when it is copied back the Control worksheet event is triggered which changes the visiblity. Try it out on a new worksheet with Sheet1 , Sheet2 , sheet3 and Control , to see how it works.

Private Sub Worksheet_Activate()
With Worksheets("Control")
inarr = .Range("B2:C300")
End With
Range("B2:C300") = inarr

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C300")) Is Nothing Then
inarr = Range("C2:C300")
With Worksheets("Control")
  .Range("C2:C300") = inarr
End With
End If
End Sub