|
Variable Print Range
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.CenterHorizont ally = True Worksheets("NamedSheet2").Activate Worksheets("NamedSheet2").PageSetup.PrintArea = Range("B1", Range("J65536").End(xlUp)) Worksheets("NamedSheet2").PageSetup.CenterHorizont ally = True Worksheets("NamedSheet3").Activate Worksheets("NamedSheet3").PageSetup.PrintArea = Range("A1", Range("I65536").End(xlUp)) Worksheets("NamedSheet3").PageSetup.CenterHorizont ally = 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 -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=509624 |
Variable Print Range
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 '<<============= --- Regards, Norman "Sami82" wrote in message ... 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.CenterHorizont ally = True Worksheets("NamedSheet2").Activate Worksheets("NamedSheet2").PageSetup.PrintArea = Range("B1", Range("J65536").End(xlUp)) Worksheets("NamedSheet2").PageSetup.CenterHorizont ally = True Worksheets("NamedSheet3").Activate Worksheets("NamedSheet3").PageSetup.PrintArea = Range("A1", Range("I65536").End(xlUp)) Worksheets("NamedSheet3").PageSetup.CenterHorizont ally = 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 -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=509624 |
Variable Print Range
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 '<<============= -- --- Regards, Norman "Norman Jones" wrote in message ... 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 '<<============= --- Regards, Norman "Sami82" wrote in message ... 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.CenterHorizont ally = True Worksheets("NamedSheet2").Activate Worksheets("NamedSheet2").PageSetup.PrintArea = Range("B1", Range("J65536").End(xlUp)) Worksheets("NamedSheet2").PageSetup.CenterHorizont ally = True Worksheets("NamedSheet3").Activate Worksheets("NamedSheet3").PageSetup.PrintArea = Range("A1", Range("I65536").End(xlUp)) Worksheets("NamedSheet3").PageSetup.CenterHorizont ally = 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 -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=509624 |
Variable Print Range
Hi Sam,
The first instance of Range("I65536") also requires a prepended dot. --- Regards, Norman |
Variable Print Range
Try using the address of your range:
Worksheets("NamedSheet1").PageSetup.PrintArea = Range("A1", Range("I65536").End(xlUp)).Address "Sami82" wrote: 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.CenterHorizont ally = True Worksheets("NamedSheet2").Activate Worksheets("NamedSheet2").PageSetup.PrintArea = Range("B1", Range("J65536").End(xlUp)) Worksheets("NamedSheet2").PageSetup.CenterHorizont ally = True Worksheets("NamedSheet3").Activate Worksheets("NamedSheet3").PageSetup.PrintArea = Range("A1", Range("I65536").End(xlUp)) Worksheets("NamedSheet3").PageSetup.CenterHorizont ally = 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 -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=509624 |
Variable Print Range
Thank you very much Norman, seemed to fix my problem :) -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=509624 |
| All times are GMT +1. The time now is 05:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com