I have the following procedure below which runs through a variable number of worksheets
and prints the same range out of each sheet onto an A4 sheet. It is not the prettiest
thing in the world but it works. However the sheet could hold two of these ranges on one
A4 page which is ideally what I would like to do, ie print one range from a sheet at the
top of the page and the next below it and so on. I would value any help.

Graham

Sub printNVZFieldsonly()

Dim r As Integer
Application.ScreenUpdating = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
r = Sheets("Entries").Cells(Rows.Count, "A").End(xlUp).row

For Each c In Sheets("Entries").Range("A12:A" & r)

' check if sheet exists
If WksExists(c.Value) Then
Sheets(c.Value).Unprotect
With Sheets(c.Value).Range("D4:E4").Font
.ColorIndex = 2
End With
With Sheets(c.Value).PageSetup
.PrintArea = "$A$3:$F$33"
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
Sheets(c.Value).PrintOut Copies:=1, Preview:=False, Collate:=True
With Sheets(c.Value).Range("D4:E4").Font
.ColorIndex = xlAutomatic
End With
Sheets(c.Value).Protect
End With
Else
MsgBox "There are no Fields to print. Operation cancelled.", vbInformation, "Print
Cancelled"
Exit Sub
End If
Next
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub

Hi Graham,

I think that the only way this can be done is to copy both the ranges to one
temporary worksheet. If the ranges contain formulas then I suggest that you
copy the range and then past special values and then paste special formats.
Note you do not have to recopy to paste special formats just place the 2
paste lines one after the other.

--
Regards,

OssieMac


"Graham H" wrote:

I have the following procedure below which runs through a variable number of worksheets
and prints the same range out of each sheet onto an A4 sheet. It is not the prettiest
thing in the world but it works. However the sheet could hold two of these ranges on one
A4 page which is ideally what I would like to do, ie print one range from a sheet at the
top of the page and the next below it and so on. I would value any help.

Graham

Sub printNVZFieldsonly()

Dim r As Integer
Application.ScreenUpdating = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
r = Sheets("Entries").Cells(Rows.Count, "A").End(xlUp).row

For Each c In Sheets("Entries").Range("A12:A" & r)

' check if sheet exists
If WksExists(c.Value) Then
Sheets(c.Value).Unprotect
With Sheets(c.Value).Range("D4:E4").Font
.ColorIndex = 2
End With
With Sheets(c.Value).PageSetup
.PrintArea = "$A$3:$F$33"
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
Sheets(c.Value).PrintOut Copies:=1, Preview:=False, Collate:=True
With Sheets(c.Value).Range("D4:E4").Font
.ColorIndex = xlAutomatic
End With
Sheets(c.Value).Protect
End With
Else
MsgBox "There are no Fields to print. Operation cancelled.", vbInformation, "Print
Cancelled"
Exit Sub
End If
Next
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub