Insert page break with the last row

[baha17]

Hi Everyone,

I have below code to set the print area and setting page break. All I want to print everything in one page. But it seems like my code does not work.
Any help?
Thanks

Baha

Sub SetPrntArea()
Dim LastRow As Long
Sheet6.Activate
With Sheet6
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
ActiveSheet.PageSetup.PrintArea = "$B$1:$P$" & LastRow
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$B$1:$P$" & LastRow
Set ActiveSheet.HPageBreaks(1).Location = Range("B" & LastRow)

End Sub

 

[Gerald Higgins]

1) In what way, exactly, does it not work ?
2) Why do you want to insert a page break AND print it all on one page ? How is this possible ? Surely by definition, if you want to print it all on one page, you want NO page breaks at all.

 

[saltkev]

Hi

this seems to work, but as the other contributor, I am Puzzled.

Dim LastRow As Long
Dim ranger As String
Sheet6.Activate
With Sheet6
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
ActiveSheet.PageSetup.PrintArea = "$B$1:$p$" & LastRow
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$B$1:$p$" & LastRow

ranger = "p" & LastRow + 1
ActiveSheet.Range(ranger).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell


Regards

Kev

 

[baha17]

Good Morning,

First of all thank you for the input and help.I think it was my mistake I post the wrong code.The code that I get error the last line was:

Set ActiveSheet.HPageBreaks(1).Location = Range("B" & LastRow+1)

In that code kept giving out of script error. Because sometimes when the data is longer, excel automatically put page breaks. So that makes continious page. What I wanted was, while printing excel to shrink into one page. I don't mind it reduces the percentage and the fonts become smaller. All I wanted was print in one page.

I still could not get it in one page. If I run the code without +1 it always break into 2 pages.


Baha

 

[saltkev]

Try this

Application.ScreenUpdating = False
Dim LastRow As Long
Dim ranger As String
Sheet6.Activate
With Sheet6
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
ActiveSheet.PageSetup.PrintArea = "$B$1:$p$" & LastRow
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.708661417322835)
.RightMargin = Application.InchesToPoints(0.708661417322835)
.TopMargin = Application.InchesToPoints(0.748031496062992)
.BottomMargin = Application.InchesToPoints(0.748031496062992)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
ActiveWindow.View = xlPageBreakPreview

 

[baha17]

Hi Saltkev,

The las code works great for me.Maybe because of my excel version (excel2003), there are some parts need to be omitted though:

'.PrintQuality = 300
'.OddAndEvenPagesHeaderFooter = False
'.DifferentFirstPageHeaderFooter = False
'.ScaleWithDocHeaderFooter = True
'.AlignMarginsHeaderFooter = True
'.EvenPage.LeftHeader.Text = ""
'.EvenPage.CenterHeader.Text = ""
'.EvenPage.RightHeader.Text = ""
'.EvenPage.LeftFooter.Text = ""
'.EvenPage.CenterFooter.Text = ""
'.EvenPage.RightFooter.Text = ""
'.FirstPage.LeftHeader.Text = ""
'.FirstPage.CenterHeader.Text = ""
'.FirstPage.RightHeader.Text = ""
'.FirstPage.LeftFooter.Text = ""
'.FirstPage.CenterFooter.Text = ""
'.FirstPage.RightFooter.Text = ""

Thank you very much for the help.
Best Regards,


Baha