Whats wrong with my loop? Only a beginner!!!

[rob_sheeds]

Hi, my loop is red and wont work. Can you suggest a change?
Thanks in advance!!!!!

Public Sub Hide_Sheets_If_Zero_Closing_Bal()
Worksheets("Summary").Activate
Range("E2").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = "0" Then
Worksheets(ActiveCell.Offset(0, -4)).Visible = False
Else
Worksheets(ActiveCell.Offset(0, -4)).Visible = True
End If
Range(ActiveCell.Offset(1, 4)).Select
Loop Until (ActiveCell.Value = "")
End Sub

 

[AlphaFrog]

Tried that but getting subscript out of range error on the line
Worksheets(cell.Offset(0, -4).Value).Visible = cell.Value <> 0

My guess is one of the cells in column A doesn't have a worksheet with the exact name in the cell. So when the line of code tries to Show\Hide that worksheet, the sheet doesn't exist.

You can try and fix the sheet name in column A or use this code which will ignore a sheet name from column A that it can't find a matching worksheet.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Hide_Sheets_If_Zero_Closing_Bal()<br>    <SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("Summary")<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> .Range("E2", .Range("E2").End(xlDown))<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            Worksheets(cell.Offset(0, -4).Value).Visible = cell.Value <> 0<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <SPAN style="color:#00007F">Next</SPAN> cell<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[rob_sheeds]

I copied that in and now pressing the button doesnt do anything.
Macro is assigned properly and security settings checked....
Checked without the Screenupdating lines too and still nothing...
Appreciate your time on this!!!

 

[AlphaFrog]

Change this line...
Worksheets(cell.Offset(0, -4).Value).Visible = cell.Value <> 0

To this...
Worksheets(CStr(cell.Offset(0, -4).Value)).Visible = cell.Value <> 0

It converts the numeric values from column A into Text to then be used as the worksheet name.

 

[rob_sheeds]

Awesome work AlphaFrog!!!!
Thanks to the other replies also.
Very grateful.
Rob