Hide Worksheet

H

Harald Staff

Hi Paul

Sub test()
With Sheets(3)
If .Range("M7").Value = "" Then
.Visible = False
Else
.Visible = True
End If
End With
End Sub

Now you figure out how to write into M7 when the sheet is hidden ;-)


HTH. Best wishes Harald
 
S

Skip

Hello,

I have created a workbook with 21 worksheets. Quite often, some of the
worksheets will not be required. Is it possible to create a macro that will
look for a value in a specific cell on a worksheet (e.g. M7) and if that
cell is blank, hide the worksheet?

Grateful for any assistance.

Paul.
 
L

Libby

Hi Paul

try this

Sub hideworksheetswithblank()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Range("m7") = "" Then
ws.Visible = xlSheetHidden
Else
ws.Visible = xlSheetVisible
End If
Next

Bear in mind that you must have at least one sheet visible
in excel, so if you try and hide them all, an error will
occur.
 
S

Skip

Thanks guys. I had to take out the 'End If' line because it gave a compile
error, but works a treat.
 
Top