Prev: Worksheet_Change?
Next: Automatically OPENing (GetObject) and Closing EXCEL via VB cal
From: markvi on 28 May 2010 10:43
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
From: Don Guillett on 28 May 2010 11:21

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
dguillett(a)gmail.com
"markvi" <markvi(a)discussions.microsoft.com> wrote in message
news:F4989A77-150D-43C8-A1D7-F0ABFB32B14B(a)microsoft.com...
>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

 | 
Pages: 1
Prev: Worksheet_Change?
Next: Automatically OPENing (GetObject) and Closing EXCEL via VB cal