Print sheets based on cell value
 

I have a workbook of about 30 sheets. Beginning with the 3rd sheet, each
sheet has a date in cell K4. I wish to print just the sheets for which the
month in K4 equals certain values.

So far I have this:

Sub PrintSheets()
Dim MonthDue As Integer
ThisWorkbook.Activate
For Each sh In ThisWorkbook.Sheets
If sh.Index 2 Then
sh.Select 'changing the activesheet
MonthDue = Month(K4)
Select Case MonthDue
Case 4
ActiveSheet.PrintOut
Case 12
ActiveSheet.PrintOut
End Select
End If
Next sh
End Sub

The problem is this: Sheet 3 has a month value of 12 and so prints. The code
cycles to Sheet 4 which has a month value of 5. The variable MonthDue is not
changing though. It's value remains 12 and so prints Sheet 4 though I don't
want it to. What am I missing that would result in MonthDue updating its
value for each sheet?

Thanks for any help.
--
- markvi

Print sheets based on cell value
 

UN tested

Sub PrintSheets()
dim sh as worksheet
for Each sh In ThisWorkbook.Sheets
with sh
if .Index 2 and _
month(.range("k4"))=4 or _
month(.range("k4"))=12 then .printout
end with
Next sh
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"markvi" wrote in message
...
I have a workbook of about 30 sheets. Beginning with the 3rd sheet, each
sheet has a date in cell K4. I wish to print just the sheets for which the
month in K4 equals certain values.

So far I have this:

Sub PrintSheets()
Dim MonthDue As Integer
ThisWorkbook.Activate
For Each sh In ThisWorkbook.Sheets
If sh.Index 2 Then
sh.Select 'changing the activesheet
MonthDue = Month(K4)
Select Case MonthDue
Case 4
ActiveSheet.PrintOut
Case 12
ActiveSheet.PrintOut
End Select
End If
Next sh
End Sub

The problem is this: Sheet 3 has a month value of 12 and so prints. The
code
cycles to Sheet 4 which has a month value of 5. The variable MonthDue is
not
changing though. It's value remains 12 and so prints Sheet 4 though I
don't
want it to. What am I missing that would result in MonthDue updating its
value for each sheet?

Thanks for any help.
--
- markvi