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!!

--
davy
-----------------------------------------------------------------------
davyb's Profile: http://www.excelforum.com/member.php...fo&userid=2640
View this thread: http://www.excelforum.com/showthread.php?threadid=39677

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

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

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

Thanks!!!


--
davyb
------------------------------------------------------------------------
davyb's Profile:
http://www.excelforum.com/member.php...o&userid=26401
View this thread: http://www.excelforum.com/showthread...hreadid=396776

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

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

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

Thanks!!!


--
davyb
------------------------------------------------------------------------
davyb's Profile:
http://www.excelforum.com/member.php...o&userid=26401
View this thread: http://www.excelforum.com/showthread...hreadid=396776

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.


--
davyb
------------------------------------------------------------------------
davyb's Profile: http://www.excelforum.com/member.php...o&userid=26401
View this thread: http://www.excelforum.com/showthread...hreadid=396776

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.)

davyb wrote:

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.

--
davyb
------------------------------------------------------------------------
davyb's Profile: http://www.excelforum.com/member.php...o&userid=26401
View this thread: http://www.excelforum.com/showthread...hreadid=396776

--

Dave Peterson

That's great Dave - thanks! - it works! So now how would I specify th
worksheets that it looks at if I don't want the macro to look at ever
worksheet in the workbook to check the value before printing that shee
or not?



Regards

Davi

--
davy
-----------------------------------------------------------------------
davyb's Profile: http://www.excelforum.com/member.php...fo&userid=2640
View this thread: http://www.excelforum.com/showthread.php?threadid=39677

Could you do it by name? Or would it be better to select the sheets first?

By name:

Option Explicit
Sub PrintCondition1()
Dim myNames As Variant
Dim w As Worksheet
myNames = Array("sheet1", "sheet2", "sheet8")
For Each w In Worksheets(myNames)
If w.Range("A9").Value 10 And _
w.Range("A9").Value < 20 Then w.PrintOut
Next
End Sub

By selecting the sheets first:

Option Explicit
Sub PrintCondition2()
Dim w As Worksheet
For Each w In ActiveWindow.SelectedSheets
If w.Range("A9").Value 10 And _
w.Range("A9").Value < 20 Then w.PrintOut
Next
End Sub



davyb wrote:

That's great Dave - thanks! - it works! So now how would I specify the
worksheets that it looks at if I don't want the macro to look at every
worksheet in the workbook to check the value before printing that sheet
or not?

Regards

David

--
davyb
------------------------------------------------------------------------
davyb's Profile: http://www.excelforum.com/member.php...o&userid=26401
View this thread: http://www.excelforum.com/showthread...hreadid=396776

--

Dave Peterson

Thanks Dave

I did it by name and attached a simple print command to the beginning
so that it always printed my first sheet and only printed the other
dependent on the value in a particular (same) cell in each of those
sheets.

Thanks for such clear help - I appreciate it.


--
davyb
------------------------------------------------------------------------
davyb's Profile: http://www.excelforum.com/member.php...o&userid=26401
View this thread: http://www.excelforum.com/showthread...hreadid=396776