Hi,
I am printing 5 different ranges using the code below (which works fine).
Each range is printed on a different page. I would like also to print range
(h3:o6) on all the pages. Any idea how to do that? Thanks



Dim W As Worksheet
Set W = Worksheets("Patients Data")

Dim Rg As Range
Set Rg = Worksheets("Print Data").Range("PrintCheckPatients")

Dim i%

Dim x As Range
Dim asRangeNames(1 To 10)
asRangeNames(1) = "$F$5"
asRangeNames(2) = "$F$180"
asRangeNames(3) = "Country1"
asRangeNames(4) = "Country2"
asRangeNames(5) = "Country3"
asRangeNames(6) = "Country4"
asRangeNames(7) = "Country5"
asRangeNames(8) = "Country6"
asRangeNames(9) = "Country7"
asRangeNames(10) = "Country8"

Dim y As Range
Dim asRangeNamesC(1 To 10)
asRangeNamesC(1) = "PrintCheckb"
asRangeNamesC(2) = "PrintCheckc"
asRangeNamesC(3) = "PrintCheck1"
asRangeNamesC(4) = "PrintCheck2"
asRangeNamesC(5) = "PrintCheck3"
asRangeNamesC(6) = "PrintCheck4"
asRangeNamesC(7) = "PrintCheck5"
asRangeNamesC(8) = "PrintCheck6"
asRangeNamesC(9) = "PrintCheck7"
asRangeNamesC(10) = "PrintCheck8"

With W.PageSetup
.Orientation = xlLandscape
.Zoom = 80
End With

If Rg = Int(True) Then

'PROCEDURE
For i = 1 To UBound(asRangeNames)

Set x = W.Range(asRangeNames(i))
Set y = Worksheets("Print Data").Range(asRangeNamesC(i))


If y.Value = Int(True) Then

W.PageSetup.PrintArea = Union(Range(x.Offset(2, 0), x.Offset(35, 9)), _
Range(x.Offset(37, 0), x.Offset(69, 9)), _
Range(x.Offset(71, 0), x.Offset(103, 9)), _
Range(x.Offset(105, 0), x.Offset(137, 9)), _
Range(x.Offset(140, 0), x.Offset(172, 9))).Address
W.PrintOut Copies:=1, Collate:=True

End If

Next i


End If


End Sub

--
caroline

Caroline

You can set the printtitlerows property for the pagesetup but this prints
rows and not ranges as in:

ActiveSheet.PageSetup.PrintTitleRows = "$3:$6"

Otherwise you can modify the worksheet before you print it, insert the area
to be printed in the five regions and then print and then remove the regions.


--
Hope this helps
Martin Fishlock


"caroline" wrote:

Hi,
I am printing 5 different ranges using the code below (which works fine).
Each range is printed on a different page. I would like also to print range
(h3:o6) on all the pages. Any idea how to do that? Thanks



Dim W As Worksheet
Set W = Worksheets("Patients Data")

Dim Rg As Range
Set Rg = Worksheets("Print Data").Range("PrintCheckPatients")

Dim i%

Dim x As Range
Dim asRangeNames(1 To 10)
asRangeNames(1) = "$F$5"
asRangeNames(2) = "$F$180"
asRangeNames(3) = "Country1"
asRangeNames(4) = "Country2"
asRangeNames(5) = "Country3"
asRangeNames(6) = "Country4"
asRangeNames(7) = "Country5"
asRangeNames(8) = "Country6"
asRangeNames(9) = "Country7"
asRangeNames(10) = "Country8"

Dim y As Range
Dim asRangeNamesC(1 To 10)
asRangeNamesC(1) = "PrintCheckb"
asRangeNamesC(2) = "PrintCheckc"
asRangeNamesC(3) = "PrintCheck1"
asRangeNamesC(4) = "PrintCheck2"
asRangeNamesC(5) = "PrintCheck3"
asRangeNamesC(6) = "PrintCheck4"
asRangeNamesC(7) = "PrintCheck5"
asRangeNamesC(8) = "PrintCheck6"
asRangeNamesC(9) = "PrintCheck7"
asRangeNamesC(10) = "PrintCheck8"

With W.PageSetup
.Orientation = xlLandscape
.Zoom = 80
End With

If Rg = Int(True) Then

'PROCEDURE
For i = 1 To UBound(asRangeNames)

Set x = W.Range(asRangeNames(i))
Set y = Worksheets("Print Data").Range(asRangeNamesC(i))


If y.Value = Int(True) Then

W.PageSetup.PrintArea = Union(Range(x.Offset(2, 0), x.Offset(35, 9)), _
Range(x.Offset(37, 0), x.Offset(69, 9)), _
Range(x.Offset(71, 0), x.Offset(103, 9)), _
Range(x.Offset(105, 0), x.Offset(137, 9)), _
Range(x.Offset(140, 0), x.Offset(172, 9))).Address
W.PrintOut Copies:=1, Collate:=True

End If

Next i


End If


End Sub

--
caroline

thanks Martin
--
caroline


"Martin Fishlock" wrote:

Caroline

You can set the printtitlerows property for the pagesetup but this prints
rows and not ranges as in:

ActiveSheet.PageSetup.PrintTitleRows = "$3:$6"

Otherwise you can modify the worksheet before you print it, insert the area
to be printed in the five regions and then print and then remove the regions.


--
Hope this helps
Martin Fishlock


"caroline" wrote:

Hi,
I am printing 5 different ranges using the code below (which works fine).
Each range is printed on a different page. I would like also to print range
(h3:o6) on all the pages. Any idea how to do that? Thanks



Dim W As Worksheet
Set W = Worksheets("Patients Data")

Dim Rg As Range
Set Rg = Worksheets("Print Data").Range("PrintCheckPatients")

Dim i%

Dim x As Range
Dim asRangeNames(1 To 10)
asRangeNames(1) = "$F$5"
asRangeNames(2) = "$F$180"
asRangeNames(3) = "Country1"
asRangeNames(4) = "Country2"
asRangeNames(5) = "Country3"
asRangeNames(6) = "Country4"
asRangeNames(7) = "Country5"
asRangeNames(8) = "Country6"
asRangeNames(9) = "Country7"
asRangeNames(10) = "Country8"

Dim y As Range
Dim asRangeNamesC(1 To 10)
asRangeNamesC(1) = "PrintCheckb"
asRangeNamesC(2) = "PrintCheckc"
asRangeNamesC(3) = "PrintCheck1"
asRangeNamesC(4) = "PrintCheck2"
asRangeNamesC(5) = "PrintCheck3"
asRangeNamesC(6) = "PrintCheck4"
asRangeNamesC(7) = "PrintCheck5"
asRangeNamesC(8) = "PrintCheck6"
asRangeNamesC(9) = "PrintCheck7"
asRangeNamesC(10) = "PrintCheck8"

With W.PageSetup
.Orientation = xlLandscape
.Zoom = 80
End With

If Rg = Int(True) Then

'PROCEDURE
For i = 1 To UBound(asRangeNames)

Set x = W.Range(asRangeNames(i))
Set y = Worksheets("Print Data").Range(asRangeNamesC(i))


If y.Value = Int(True) Then

W.PageSetup.PrintArea = Union(Range(x.Offset(2, 0), x.Offset(35, 9)), _
Range(x.Offset(37, 0), x.Offset(69, 9)), _
Range(x.Offset(71, 0), x.Offset(103, 9)), _
Range(x.Offset(105, 0), x.Offset(137, 9)), _
Range(x.Offset(140, 0), x.Offset(172, 9))).Address
W.PrintOut Copies:=1, Collate:=True

End If

Next i


End If


End Sub

--
caroline