Variable Print Range

[Sami82]

Hi All,

I am writing a small piece of some very large code and I have come
across a glitch. In this one piece I set the print ranges for each
sheet:

Worksheets("NamedSheet1").Activate
Worksheets("NamedSheet1").PageSetup.PrintArea = Range("A1",
Range("I65536").End(xlUp))
Worksheets("NamedSheet1").PageSetup.CenterHorizontally = True
Worksheets("NamedSheet2").Activate
Worksheets("NamedSheet2").PageSetup.PrintArea = Range("B1",
Range("J65536").End(xlUp))
Worksheets("NamedSheet2").PageSetup.CenterHorizontally = True
Worksheets("NamedSheet3").Activate
Worksheets("NamedSheet3").PageSetup.PrintArea = Range("A1",
Range("I65536").End(xlUp))
Worksheets("NamedSheet3").PageSetup.CenterHorizontally = True

Error: 1004 Unable to set the PrintArea property of the pagesetup
class.


But it doesnt seem to like the second or third times.

Can anyone help me? Thank you

 

[Norman Jones]

Hi Sam,

The PrintArea property requires an address string. Additionally, it is
unnecessary to activate each sheet.

Try:
'=============>>
Public Sub Tester5()

With Worksheets("NamedSheet1")
.PageSetup.PrintArea = Range("A1", _
Range("I65536").End(xlUp)).Address
.PageSetup.CenterHorizontally = True
End With

With Worksheets("NamedSheet2")
.PageSetup.PrintArea = Range("B1", _
Range("J65536").End(xlUp)).Address
.PageSetup.CenterHorizontally = True
End With

With Worksheets("NamedSheet3")
.PageSetup.PrintArea = Range("A1", _
Range("I65536").End(xlUp)).Address
.PageSetup.CenterHorizontally = True
End With


End Sub
'<<=============

 

[Norman Jones]

Hi Sam,

The ranges also need to be qualified:

'=============>>
Public Sub Tester5A()

With Worksheets("NamedSheet1")
.PageSetup.PrintArea = .Range("A1", _
Range("I65536").End(xlUp)).Address
.PageSetup.CenterHorizontally = True
End With

With Worksheets("NamedSheet2")
.PageSetup.PrintArea = .Range("B1", _
.Range("J65536").End(xlUp)).Address
.PageSetup.CenterHorizontally = True
End With

With Worksheets("NamedSheet3")
.PageSetup.PrintArea = .Range("A1", _
.Range("I65536").End(xlUp)).Address
.PageSetup.CenterHorizontally = True
End With


End Sub
'<<=============

 

[Norman Jones]

Hi Sam,


The first instance of Range("I65536") also requires a prepended dot.

 

[Guest]

Try using the address of your range:

Worksheets("NamedSheet1").PageSetup.PrintArea = Range("A1",
Range("I65536").End(xlUp)).Address

 

[Sami82]

Thank you very much Norman, seemed to fix my problem