I have a few reports that need to be printed daily, There are some that are
the same everyday, I have no problems with them, I have a daily inventory
that needs to be printed out for the day that it is recorded. I have a macro
that is to print the inventory automatically, but it is not working as I had
hoped. Look it over and see where my problem may be. Any help would be
greatly appreciated.

Sheets("Week 2").Select
With ActiveSheet
If .Range("L7").Value = "yes" Then
Sheets("Daily Inventory Friday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L9").Value = "yes" Then
Sheets("Daily Inventory Saturday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L11").Value = "yes" Then
Sheets("Daily Inventory Sunday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L13").Value = "yes" Then
Sheets("Daily Inventory Monday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L15").Value = "yes" Then
Sheets("Daily Inventory Tuesday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L17").Value = "yes" Then
Sheets("Daily Inventory Wednesday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L19").Value = "yes" Then
Sheets("Daily Inventory Thursday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
End Sub

The logic goes something as:

If the following ranges contain the word "yes" (and it's case sensitive)
then print related sheet.

L7: Daily Inventory Friday 2
L9: Daily Inventory Saturday 2
L11: Daily Inventory Sunday 2
L13: Daily Inventory Monday 2
L15: Daily Inventory Tuesday 2
L17: Daily Inventory Wednesday 2
L19: Daily Inventory Thursday 2

Hope that helps

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"justaguyfromky" wrote in message
...
I have a few reports that need to be printed daily, There are some that are
the same everyday, I have no problems with them, I have a daily inventory
that needs to be printed out for the day that it is recorded. I have a
macro
that is to print the inventory automatically, but it is not working as I
had
hoped. Look it over and see where my problem may be. Any help would be
greatly appreciated.

Sheets("Week 2").Select
With ActiveSheet
If .Range("L7").Value = "yes" Then
Sheets("Daily Inventory Friday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L9").Value = "yes" Then
Sheets("Daily Inventory Saturday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L11").Value = "yes" Then
Sheets("Daily Inventory Sunday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L13").Value = "yes" Then
Sheets("Daily Inventory Monday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L15").Value = "yes" Then
Sheets("Daily Inventory Tuesday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L17").Value = "yes" Then
Sheets("Daily Inventory Wednesday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L19").Value = "yes" Then
Sheets("Daily Inventory Thursday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
End Sub

"With Activsheet" is always referring to Week 2

Be more specific and don't rely on selecting.

With Sheets("Week 2")
If .Range("L7").Value = "yes" Then
Sheets("Daily Inventory Friday 2").PrintOut Copies:=1, Collate:=True
End If
If .Range("L9").Value = "yes" Then
Sheets("Daily Inventory Saturday 2").PrintOut Copies:=1,
Collate:=True
End If

...
End With

You also need to be careful with "yes". It must not be capitalised in the
worksheet because "Yes"<"yes" under normal circumstances.


--
John Green
Sydney
Australia


"justaguyfromky" wrote in message
...
I have a few reports that need to be printed daily, There are some that
are
the same everyday, I have no problems with them, I have a daily inventory
that needs to be printed out for the day that it is recorded. I have a
macro
that is to print the inventory automatically, but it is not working as I
had
hoped. Look it over and see where my problem may be. Any help would be
greatly appreciated.

Sheets("Week 2").Select
With ActiveSheet
If .Range("L7").Value = "yes" Then
Sheets("Daily Inventory Friday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L9").Value = "yes" Then
Sheets("Daily Inventory Saturday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L11").Value = "yes" Then
Sheets("Daily Inventory Sunday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L13").Value = "yes" Then
Sheets("Daily Inventory Monday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L15").Value = "yes" Then
Sheets("Daily Inventory Tuesday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L17").Value = "yes" Then
Sheets("Daily Inventory Wednesday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
With ActiveSheet
If .Range("L19").Value = "yes" Then
Sheets("Daily Inventory Thursday 2").Select
.PrintOut Copies:=1, Collate:=True
End If
End With
Sheets("Week 2").Select
End Sub

Hi,

justaguyfromky wrote:
I have a few reports that need to be printed daily, There are some that are
the same everyday, I have no problems with them, I have a daily inventory
that needs to be printed out for the day that it is recorded. I have a macro
that is to print the inventory automatically, but it is not working as I had
hoped. Look it over and see where my problem may be. Any help would be
greatly appreciated.
[snipp Code]

here is still another further possibility with a loop.

Call WeekPrinting(2)

Public Sub WeekPrinting(intWeek As Integer)
Dim varDay As Variant
Dim lngRow As Long
Dim strWeekDay As String
Dim intDay As Integer

varDay = Array(5, 6, 7, 1, 2, 3, 4)

With Worksheets("Week " & intWeek)
For lngRow = 7 To 19 Step 2
If .Range("L" & lngRow).Value = "yes" Then
strWeekDay = WeekdayName(varDay(intDay)) & " " & intWeek
Sheets("Daily Inventory " & strWeekDay).PrintOut
End If
intDay = intDay + 1
Next lngRow
End With
End Sub

--
Regards

Melanie Breden
- Microsoft MVP für Excel -