Conditional Hide/Print Worksheet

[davyb]

Can anyone tell me how I can create a button on one worksheet that wil
print that and other worksheets in the file that have a specified valu
range in a specified field on each worksheet?

Similarly I need to show or hide worksheets dependent on teh value in
specified field in another worksheet. This should not be activated by
button but by the value in the specified field being entered so that e
a worksheet that is hidden by default will be revealed should the valu
in the specified field of another worksheet be a specified amount.

Thanks!!

 

[Mike Fogleman]

Here is the first part to print by condition

Sub PrintCondition()
Dim w As Worksheet
For Each w In Worksheets
If Range("A9").Value > 10 And _
Range("A9").Value < 20 Then w.PrintOut
Next
End Sub

It would be best to put this in a general module and call it from the
button_click event.

Mike F

 

[Mike Fogleman]

Part 2 example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("H15").Value = "" Then Exit Sub
If Range("H15").Value = 2 Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
End Sub

This will be in the worksheet module

Mike F

 

[davyb]

Thanks Mike - almost there! - that prints out all of the sheets of the
work book or doesn't print at all according to the value in one
specified cell of the current worksheet.

I would like to print out specified worksheets dependent on the value
in one cell.

So for example if the value =<7 then worksheets 1-4 get printed out. If
the value is >7=<14 then worksheets 1-5 get printed out and so on.

Is this possible do you think?

Another way to frame the logic would be a macro that always prints out
eg sheets 1-3 and looks at the same cell on each sheet 4 and above to
see if there is a value there and if it is there that sheet gets
printed, if not that sheet does not get printed.

 

[Dave Peterson]

Try this modification of Mike's suggestion:

Sub PrintCondition()
Dim w As Worksheet
For Each w In Worksheets
If w.Range("A9").Value > 10 And _
w.Range("A9").Value < 20 Then w.PrintOut
Next
End Sub

(Without the reference to w (w.range("a9")), the code is always checking the
activesheet's A9.)