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

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 _
Befo=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 Befo=Range("A55")

HTH,
--
Gary Brown

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!!!!!

:) :) :) :) :) :) :)


--
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