Setting The Print Area

Hi,

I'm quite new to the VBA side of excel, and i'm having a nightmare with this bit of code. What I want it to do is when you click print to set the print area to $A$2:$AZ$90 and then for it to print landscape on two sheets of paper, with the page break being at A54. I have been trying to do this for weeks now and have had so many different pieces of code etc I'm now left with this, which doesn't work at all :

Please Login or Register  to view this content.

If it helps I can send the Workbook which I'm using or anwser any questions. Please please please can anyone help!!!!!!

Thanks

Dave

Maybe...

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)

'turn off screen flickering
Application.ScreenUpdating = False
'print procedure
With ActiveSheet.PageSetup
.PrintArea = "$A$2:$az$90"
.Orientation = xlLandscape
.CenterHeader = "&U&26AV Bookings Week Commencing " & ActiveSheet.Name
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.BlackAndWhite = False
.PrintErrors = xlPrintErrorsDisplayed
End With

'add in page break
ActiveSheet.HPageBreaks.Add _
Before:=ActiveSheet.Range("a54")

'turn off screen flickering
Application.ScreenUpdating = True
End Sub


beans_21 wrote:
>
> Hi,
>
> I'm quite new to the VBA side of excel, and i'm having a nightmare with
> this bit of code. What I want it to do is when you click print to set
> the print area to $A$2:$AZ$90 and then for it to print landscape on two
> sheets of paper, with the page break being at A54. I have been trying
> to do this for weeks now and have had so many different pieces of code
> etc I'm now left with this, which doesn't work at all :
>
> Code:
> --------------------
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>
> 'turn off screen flickering
> Application.ScreenUpdating = False
> 'print procedure
> With ActiveSheet.PageSetup
> .PrintArea = "$A$2:$ay$90"
> .Orientation = xlLandscape
> .CenterHeader = "&U&26AV Bookings Week Commencing " & (Application.ActiveSheet.Name)
> .PrintHeadings = False
> .PrintGridlines = False
> .PrintComments = xlPrintNoComments
> .BlackAndWhite = False
> .PrintErrors = xlPrintErrorsDisplayed
> End With
>
> 'add in page break
> Set ActiveSheet.HPageBreaks(1).Location = Range("A54")
>
> 'turn off screen flickering
> Application.ScreenUpdating = True
> End Sub
> --------------------
>
> If it helps I can send the Workbook which I'm using or anwser any
> questions. Please please please can anyone help!!!!!!
>
> Thanks
>
> Dave
>
> --
> beans_21
> ------------------------------------------------------------------------
> beans_21's Profile: http://www.excelforum.com/member.php...o&userid=30281
> View this thread: http://www.excelforum.com/showthread...hreadid=499519

--

Dave Peterson

I suspect that since you (a) want to print on 2 sheets, range A1 to AY54 and
range A55 to AY90, you have your worksheet set to...
.FitToPagesWide = 1
otherwise, you normally can't fit columns A thru AY on one sheet even in
landscape, and (b) if this is true, Page Break doesn't work.

To get around this, we need to know what the proper % scaling would be to
print landscape across 1 page...
1) go into page setup and make sure that 'Scaling' is selected and set to
100%
2) hit OK to get out of page setup
3) get back into page setup and select 'Fit to' 1 wide by 2 tall
4) hit OK to get out of page setup
5) get back into page setup and select 'Scaling'
6) note what the % is that Excel has automatically calculated is necessary
for making the worksheet appear 1 page wide.

Let's say it says 60%.

These lines should be inside the WITH statement...
.FitToPagesWide = False 'to make sure that Fit To is NOT selected
.Zoom = 60 'or whatever the correct % is

Now, the Page Break statement should work. I tried it with ...
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A55")

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"beans_21" wrote:

>
> Hi,
>
> I'm quite new to the VBA side of excel, and i'm having a nightmare with
> this bit of code. What I want it to do is when you click print to set
> the print area to $A$2:$AZ$90 and then for it to print landscape on two
> sheets of paper, with the page break being at A54. I have been trying
> to do this for weeks now and have had so many different pieces of code
> etc I'm now left with this, which doesn't work at all :
>
>
> Code:
> --------------------
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>
> 'turn off screen flickering
> Application.ScreenUpdating = False
> 'print procedure
> With ActiveSheet.PageSetup
> .PrintArea = "$A$2:$ay$90"
> .Orientation = xlLandscape
> .CenterHeader = "&U&26AV Bookings Week Commencing " & (Application.ActiveSheet.Name)
> .PrintHeadings = False
> .PrintGridlines = False
> .PrintComments = xlPrintNoComments
> .BlackAndWhite = False
> .PrintErrors = xlPrintErrorsDisplayed
> End With
>
> 'add in page break
> Set ActiveSheet.HPageBreaks(1).Location = Range("A54")
>
> 'turn off screen flickering
> Application.ScreenUpdating = True
> End Sub
> --------------------
>
>
> If it helps I can send the Workbook which I'm using or anwser any
> questions. Please please please can anyone help!!!!!!
>
> Thanks
>
> Dave
>
>
> --
> beans_21
> ------------------------------------------------------------------------
> beans_21's Profile: http://www.excelforum.com/member.php...o&userid=30281
> View this thread: http://www.excelforum.com/showthread...hreadid=499519
>
>

Thanks a lot the zoom tip was ace! Now works a treat! THANK YOU THANK YOU THANK YOU!!!!!