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.

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

"Skip" skrev i melding
...
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.

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.


-----Original Message-----
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

"Skip" skrev i melding
...
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.




.

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

"Libby" wrote in message
...
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.


-----Original Message-----
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

"Skip" skrev i melding
...
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.




.