Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Variable Print Range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Variable Print Range
Hi Sam,


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

---
Regards,
Norman


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
  
Posts: 2,062
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using named range to extend print area for variable number of columns Pierre Excel Worksheet Functions 3 April 10th 08 05:51 PM
Macro in VBA: Setting a variable print range HELP NEEDED!! Ron de Bruin Excel Programming 1 July 21st 04 05:45 PM
setting a range variable equal to the value of a string variable Pilgrim Excel Programming 2 July 1st 04 11:32 PM
Problem trying to us a range variable as an array variable TBA[_2_] Excel Programming 4 September 27th 03 02:56 PM
Setting up a variable print range Gary[_5_] Excel Programming 5 July 31st 03 04:32 AM


All times are GMT +1. The time now is 11:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement