Setting The Print Area

[beans_21]

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

 

[Dave Peterson]

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

 

[Gary L Brown]

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,

 

[beans_21]

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