Adjustable print range
 

Morning,

I have a file with 11 sheets and I need a macro to print sheets 2 and 4-11.
The print ranges are changeable every time on sheets 4 to 11.

Sheet 2 I need to print A20:N34
Sheets 4 to 11 I need to print A1:OX

In order to find the X I have a list of numbers in col R. Starting in row 1
to row X I have numbers greater than 0. Row X will have a number 0 and row
X+1 = 0, always.

Example 1 €“ in col R I have numbers that do 8 8 8 8 1 2 3 4 5 6 7 8 9 0 0 0
0 0 0 - print range needed is A1:O13
Example 2 €“ in col R I have numbers that do 3 3 3 3 1 2 3 4 0 0 0 0 0 0 -
print range needed A1:O8

In order to find the last row it can either be done by finding the cell at
which the R col changes to zero or the row at which max(R1:R100) occurs.

Is there a way to set a code to find the row reference I need and set an
adjustable rpint range accordingly?

Thanks
LiAD

Adjustable print range
 

Try the below

Sub Macro()
Dim intSheet As Integer, lngRow As Long

Sheets(2).Range("A20:N34").PrintOut Copies:=1, Collate:=True
For intSheet = 4 To 11
lngRow = Evaluate("=MAX(IF('" & Sheets(intSheet).Name & _
"'!R1:R100<0,ROW('" & Sheets(intSheet).Name & "'!R1:R100)))")
If lngRow < 0 Then
Sheets(intSheet).Range("A1:O" & lngRow).PrintOut Copies:=1, Collate:=True
End If
Next

End Sub


--
Jacob


"LiAD" wrote:

Morning,

I have a file with 11 sheets and I need a macro to print sheets 2 and 4-11.
The print ranges are changeable every time on sheets 4 to 11.

Sheet 2 I need to print A20:N34
Sheets 4 to 11 I need to print A1:OX

In order to find the X I have a list of numbers in col R. Starting in row 1
to row X I have numbers greater than 0. Row X will have a number 0 and row
X+1 = 0, always.

Example 1 €“ in col R I have numbers that do 8 8 8 8 1 2 3 4 5 6 7 8 9 0 0 0
0 0 0 - print range needed is A1:O13
Example 2 €“ in col R I have numbers that do 3 3 3 3 1 2 3 4 0 0 0 0 0 0 -
print range needed A1:O8

In order to find the last row it can either be done by finding the cell at
which the R col changes to zero or the row at which max(R1:R100) occurs.

Is there a way to set a code to find the row reference I need and set an
adjustable rpint range accordingly?

Thanks
LiAD

Adjustable print range
 

Perfecto

Thanks a lot

"Jacob Skaria" wrote:

Try the below

Sub Macro()
Dim intSheet As Integer, lngRow As Long

Sheets(2).Range("A20:N34").PrintOut Copies:=1, Collate:=True
For intSheet = 4 To 11
lngRow = Evaluate("=MAX(IF('" & Sheets(intSheet).Name & _
"'!R1:R100<0,ROW('" & Sheets(intSheet).Name & "'!R1:R100)))")
If lngRow < 0 Then
Sheets(intSheet).Range("A1:O" & lngRow).PrintOut Copies:=1, Collate:=True
End If
Next

End Sub


--
Jacob


"LiAD" wrote:

Morning,

I have a file with 11 sheets and I need a macro to print sheets 2 and 4-11.
The print ranges are changeable every time on sheets 4 to 11.

Sheet 2 I need to print A20:N34
Sheets 4 to 11 I need to print A1:OX

In order to find the X I have a list of numbers in col R. Starting in row 1
to row X I have numbers greater than 0. Row X will have a number 0 and row
X+1 = 0, always.

Example 1 €“ in col R I have numbers that do 8 8 8 8 1 2 3 4 5 6 7 8 9 0 0 0
0 0 0 - print range needed is A1:O13
Example 2 €“ in col R I have numbers that do 3 3 3 3 1 2 3 4 0 0 0 0 0 0 -
print range needed A1:O8

In order to find the last row it can either be done by finding the cell at
which the R col changes to zero or the row at which max(R1:R100) occurs.

Is there a way to set a code to find the row reference I need and set an
adjustable rpint range accordingly?

Thanks
LiAD