Multi-sheet Printing

[jwongsf]

Hi guys, I know that there is an easier and shorter way of doing this;

 
Sub Print_PET()
 
'
' PrintMeetingForm Macro
'
 
Range("V2").Select
Sheets("PAGE (1)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
If Sheet11.Cells(3, 8) >= 2 Then
Sheets("Page (2)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet5.Cells(3, 8) >= 3 Then
Sheets("Page (3)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet6.Cells(3, 8) >= 4 Then
Sheets("Page (4)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet7.Cells(3, 8) >= 5 Then
Sheets("Page (5)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
 
If Sheet8.Cells(3, 8) >= 6 Then
Sheets("Page (6)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet9.Cells(3, 8) >= 7 Then
Sheets("Page (7)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet10.Cells(3, 8) >= 8 Then
Sheets("Page (8)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet11.Cells(3, 8) >= 9 Then
Sheets("Page (9)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet12.Cells(3, 8) >= 10 Then
Sheets("Page (10)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet13.Cells(3, 8) >= 11 Then
Sheets("Page (11)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet14.Cells(3, 8) >= 12 Then
Sheets("Page (12)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet15.Cells(3, 8) >= 13 Then
Sheets("Page (13)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet15.Cells(3, 8) >= 14 Then
Sheets("Page (14)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
 
If Sheet16.Cells(3, 8) >= 15 Then
Sheets("Page (15)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet17.Cells(3, 8) >= 16 Then
Sheets("Page (16)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
 
 
 
Sheets("Attach E").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Call RTN_DATA
 
Sheets("Page (1)").Select
End Sub

and I am think along the lines of 2 for and next statement however I am unclear of the syntax for the sheets, to get it to be variable.
Thanks James

 
sub Print_RTN()
dim k= interger, i = tinterger
for k = 1 to 17
i =-2 to 16
 
if sheet("k").Cells(3, 8) >= i Then
Sheets("Page ("k")").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If exit
next k
next i
 
call rtn_data
end

[tony h]

You could do it this way (I haven't put all the sheets in the "array" statement but just carry on with the format

Option Explicit

Sub Print_PET()

'
' PrintMeetingForm Macro
'

Range("V2").Select
Sheets("PAGE (1)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Dim VArray() As Variant
Dim var As Variant

VArray = Array(Array("Sheet11", 2, "Page (2)"), Array("Sheet5", 3, "Page (3)"), Array("Sheet6", 4, "Page (4)"))

For Each var In VArray()
    If Worksheets(var(0)).Cells(3, 8) >= var(1) Then
        Sheets(var(2)).Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
Next


Sheets("Attach E").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Call RTN_DATA

Sheets("Page (1)").Select
End Sub

[VBA Noob]

jwongsf & tony h,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[jwongsf]

Okay since I do not understand about the coding issue, then Let me try this is plain thoughts.
I would like to create a macro that woudl printout work sheets 1 -16 depending on whether the page number is used, as denoted by a particular cell value. I am leaning towards 2 for and next statement, one for the worksheet and one to compare the cell value, I am unclear as to how to setup the for and next in conjunction with ths worksheet(1) so that I can get it to variable number to change.
Thanks
James

[tony h]

I will try and explain wha I have done. The main part is to take the original code:

If Sheet11.Cells(3, 8) >= 2 Then
Sheets("Page (2)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet5.Cells(3, 8) >= 3 Then
Sheets("Page (3)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet6.Cells(3, 8) >= 4 Then
Sheets("Page (4)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet7.Cells(3, 8) >= 5 Then
Sheets("Page (5)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If

and replace it with a loop:

VArray = Array(Array("Sheet11", 2, "Page (2)"), Array("Sheet5", 3, "Page (3)"), Array("Sheet6", 4, "Page (4)"))
 
For Each var In VArray()
    If Worksheets(var(0)).Cells(3, 8) >= var(1) Then
        Sheets(var(2)).Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
Next

So how does it work? Well: in the original repeating sections there are three items that change. I have marked them in red below:

 
If Sheet11.Cells(3, 8) >= 2 Then
Sheets("Page (2)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
If Sheet5.Cells(3, 8) >= 3 Then
Sheets("Page (3)").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If

These I have picked out and placed in the nested array:
First block:

Array( "Sheet11" , 2 , "Page (2)" )

Second block:

Array( "Sheet5" , 3 , "Page (3)" )

and so on.

These blocks are then bundled into another array:

 
array( block1 , block2 , etc )

Array(Array("Sheet11", 2, "Page (2)"), Array("Sheet5", 3, "Page (3)"), Array("Sheet6", 4, "Page (4)"))

Then the remaining code uses this data:

For Each var In VArray()
    If Worksheets(var(0)).Cells(3, 8) >= var(1) Then
        Sheets(var(2)).Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
Next

For Each var in vArray()

basically steps through each block of data setting the variable var to contain the data.

var now contains an array of three items. The three items are identified as: var(0), var(1), and var(2). Which for the first block will contain :

 
var(0) = "Sheet11"
var(1) = 2
var(2) = "Page (2)"

So you can see, I hope, that as the variables contain the values from the block the code will look like the original:

 
If Worksheets(var(0)).Cells(3, 8) >= var(1) Then
Sheets(var(2)).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If

I hope this helps

[jwongsf]

Thanks Tonyh and for the explanation, also sorry for the infraction.