Code is not working....please take a look!!!

[tratliff]

I'm having some problems with my code.

Here is what is NOT working:

I'm not getting Sheet2 thru Sheet5...it is only printing Sheet1...an
suggestions as to why??

Also, my message box is not popping up if the cell is blank???



If Range("l12") = 2 Then
Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5"
"sheet6")).Select
Range("ac1:ax79").PrintOut
If Range("l12") = 3 Then
Sheets(Array("lays", "bistro", "kettle", "lay
lights", "stax", "ruffles")).Select
Range("ay1:bt79").PrintOut
If Range("L12") = 4 Then
Sheets(Array("lays", "bistro"
"kettle", "lays lights", "stax", "ruffles")).Select
Range("bu1:cw79").Print
If Range("l12") = " " Then
MsgBox ("Please enter quarte
you wish to print in cell L12 on the Menu tab")
End If
End If
End If
End I

 

[E_R]

If Range("l12") = 2 Then
Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5"
"sheet6")).Select
Range("ac1:ax79").PrintOut
End If

If Range("l12") = 3 Then
Sheets(Array("lays", "bistro", "kettle", "lays lights", "stax"
"ruffles")).Select
Range("ay1:bt79").PrintOut
End If

If Range("L12") = 4 Then
Sheets(Array("lays", "bistro", "kettle", "lays lights", "stax"
"ruffles")).Select
Range("bu1:cw79").Print
End If

If Range("l12") = "" Then
MsgBox ("Please enter quarter you wish to print in cell L12 on the Men
tab")
End I

 

[E_R]

Better yet...


Select Case Range("l12")
Case Is = 2
Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5"
"sheet6")).Select
Range("ac1:ax79").PrintOut
Case Is = 3
Sheets(Array("lays", "bistro", "kettle", "lays lights", "stax"
"ruffles")).Select
Range("ay1:bt79").PrintOut
Case Is = 4
Sheets(Array("lays", "bistro", "kettle", "lays lights", "stax"
"ruffles")).Select
Range("bu1:cw79").Print
Case Is = ""
MsgBox ("Please enter quarter you wish to print in cell L12 on th
Menu tab")
End Selec

 

[tratliff]

It's still only printing Sheet1..

 

[E_R]

Try this...

Select Case Range("l12")
Case Is = 2
Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5"
"sheet6")).Select
Selection.Range("ac1:ax79").PrintOut
Case Is = 3
Sheets(Array("lays", "bistro", "kettle", "lays lights", "stax"
"ruffles")).Select
Selection.Range("ay1:bt79").PrintOut
Case Is = 4
Sheets(Array("lays", "bistro", "kettle", "lays lights", "stax"
"ruffles")).Select
Selection.Range("bu1:cw79").Print
Case Is = ""
MsgBox ("Please enter quarter you wish to print in cell L12 on the Men
tab")
End Selec

 

[Dianne Butterworth]

How about:

Sub test()

Dim sht As Worksheet

Select Case Worksheets("Menu").Range("L12").Value
Case 2
For Each sht In Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4",
"sheet5", "sheet6"))
sht.Range("ac1:ax79").PrintOut
Next sht
Case 3
For Each sht In Sheets(Array("lays", "bistro", "kettle",
"layslights", "stax", "ruffles"))
sht.Range("ay1:bt79").PrintOut
Next sht
Case 4
For Each sht In Sheets(Array("lays", "bistro", "kettle", "lays
lights", "stax", "ruffles"))
sht.Range("bu1:cw79").PrintOut
Next sht
Case Else
MsgBox ("Please enter quarter you wish to print in cell L12 on the
Menu tab")
End Select

End Sub