Print sheets based on cell value

  • Thread starter Thread starter markvi
  • Start date Start date
M

markvi

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.
 
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
 
Back
Top