Print sheets based on cell value

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

Don Guillett

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top