Hiding individual worksheets with code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook which contains 40+ sheets that is filled out by our vendors
and returned to us. Not all sheets are used every time and I would like to
hide the unused sheets before submitting to document control.
Every sheet has information on it as there are cell references on each sheet
to basic job information filled out on sheet one.
I was thinking about a macro or VBA that runs behind a hidden command button
that would determine if certain fields on each sheet were filled or empty,
and hide the sheets that had the empty fields.
All of the sheets are protected, so obviously the code would have to
unprotect the sheets before hiding.

I know what end result I would like, but…
I have no idea where to start!
 
try this idea
Sub hideif()
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then
If Len(ws.Range("a1")) < 1 Then ws.Visible = False
End If
Next
End Sub
 
We can start from here:-

Sub sonic()
'Scroll through worksheets
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If Range("A1").Value = "" Then ws.Visible = False
Next ws
End Sub

This will loop through all worksheets and hide them if A1 is empty. Two
points. It will fail if A1 is empty in all sheets because you can't hide all
sheets and 2 you don't have to unprotect a protected sheet to hide it.

Mike
 
Back
Top