Free Microsoft Excel 2013 Quick Reference

Set Page Break In Macro

I am having an awful hard time getting VBa to set my page breaks. I have used the


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to reset them and have tried everything I have been able to find on this and other forums. has anyone else been finding that excel just doesn't do ANYTHING when they set page breaks via VBA? Or have you found a solution to the problem?

Thanks ahead of time,

Post your answer or comment

comments powered by Disqus
I cannot do manual, or drag and drop, page breaks in Excel. A large white
cross appears when I cursor over a cell. I cannot find a way to enlarge a
page break/print area, nor to make it narrower, except by using "set print
area" which is not the same as dragging the print area boundary.
I used to be able to do this!! Can anyone help? Thanks.

I am trying to het a macro I am writing to apply page breaks where I want them.

I used this command:

Set ActiveSheet.HPageBreaks(1).Location = Range("A87")
Set ActiveSheet.HPageBreaks(2).Location = Range("A175")
Set ActiveSheet.HPageBreaks(3).Location = Range("A266")

I get a message:

"run time error (9)
Subscripts out of range"

When I hit the dubug option, it highlights the row:
Set ActiveSheet.HPageBreaks(3).Location = Range("A266")

Will Excel only allow for 3 page breaks in macros?

I used to be able to drag and drop page breaks in Excel. I can no longer do
that on my system. Does anyone know why? I can "reset print area" but this
isn't the same and does not have the same outcome.

Hey Everyone,

This spreadsheet has two tables with years and years of data on each. Separating the two tables is a blank column. Only the last 12 months are visible on each table as the past data is hidden.

Is there any way to insert page breaks when macro encounters a blank column in only the visible data.

Hopefully, the final result (if printed) should be two pages with one table on each page.

I found this code below, but I'm too much of a noob to figure out what to do. If you guys can point me in the right direction, it would be greatly appreciated.

     'Turn off screen updating
    Application.ScreenUpdating = False 
     'Use this to remove all existing page breaks and
    With ActiveSheet.PageSetup 
        .FitToPagesWide = 1 
        .FitToPagesTall = 1 
        .Orientation = xlLandscape 
    End With 
     'Procedure level declarations
    Dim lRow As Long 
    Dim lLastRow As Long 
    Dim iLastCol As Integer 
    Dim lPgeBreak As Long 
     'Determine the row an column parameters of the active sheet
    If Not Cells.Find("*", , xlValues, , xlByRows, xlPrevious) Is Nothing Then 
        lLastRow = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row 
        iLastCol = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Column 
         'Restore Screen updating
        Application.ScreenUpdating = True 
        Exit Sub 'Blank worksheet
    End If 
     'Convert window view into PageBreakPreview mode
    ActiveWindow.View = xlPageBreakPreview 
     'Set the current print area to include entire Columns A through to the last non blank column
    ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(ActiveSheet.Rows.Count, iLastCol)).Address 
     'I started this loop at your first non blank row in column A
     'You many want to include some code here to derive your starting point
    For lRow = 3 To lLastRow 
         'Determine whether the current cell is blank
         'NB that this whole loop could be done quicker
         'with proper use of the Find function e.g. skip to
         'next blank rather then test each cell value
        If Cells(lRow, 1).Value = "" Then 
             'Set a counter for the number of page breaks added
            lPgeBreak = lPgeBreak + 1 
             'This is to avoid a clash with the existing end page break
            If lRow  lLastRow Then 
                 'Remove default error handling for page breaks
                On Error Resume Next 
                 'Set the Horizontal page break location
                Set ActiveSheet.HPageBreaks(lPgeBreak).Location = Cells(lRow, 1) 
                 'If the above method fails then try the add method
                If Err.Number  0 Then 
                    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(lRow, 1) 
                End If 
                 'Restore default error handling
                On Error Goto 0 
            End If 
        End If 
         'Continue loop
    Next lRow 
     'Restore normal view
    ActiveWindow.View = xlNormalView 
     'Restore screen updating
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



Please, can anyone help me with the shortcomings mentioned here about a "page-break" instruction in a Words document in a code when running in Excel.

In addition, the normal code in Words for MoveUp" does not work, I want to get up into an imported table with data and adjust the characteristics of the table in order to fit the page in Words.

Thanks in advance for kind assistance
/ bjorn

This is my very first attempt to seek advice in programming VBA.
Background: XP professional, Office 2000

The context & the problem :

Running a long program in Excel with many macros / coded subs in background.
The program will result in a lot of reports, typically of 5-25 pages each & with a lot of conditional text created in excel macros / VBA
I want (must) get print out in Words as such printout is smoother.
Everything is - be the end of a lot of work - OK, except to make a page-break in the excel routine that runs what to be done in the created word document.
Some instructions are OK, for example to rearrange margins, others, for example pagehead and pagefoot does not work, but WORST:
In words, the "instruction" for page break is - .InsertBreak Type:=wdPageBreak - to be put within With wrd (= stands for Words..) and With .selection.
In words, it works, but when running from Excel, it does NOT work.

The code under Excel used for this small program sequence is found below
' Open a document
Set wrd = GetObject(, "Word.Application"):

With wrd:
With .Selection
.TypeText Text:="This is page 1"

'.Collapse Direction:=wdCollapseEnd
'.InsertBreak Type:=wdPageBreak ' tried - did not work

'.InsertBreak Type:=wdSectionBreakContinuous, ' tried - did not work
.TypeText Text:="Some text on page 2"
.Sections.Add ' this works, but text instructions after this command IS NOT placed after a page break
' .MoveEndUntil Cset:="a ", Count:=wdForward, tried.... did not work
' .MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.TypeText Text:="Here should be text, for example to state that we are now on page 2..."
End With
End With

Is it possible to have some help on this (trivial ??) matter ?
Regards Bjorn

I have this macro that cleans up the active worksheet that a user chooses to run the macro on (from a toolbar button). It changes the column heading, wraps a long column, autofits the text and sets the page setup.

An issue that I am having is with the page breaks. I need to sort the sheet by column B, and then break the pages on column B's value. So if it is sorted and Column B, Rows 1-3 have the value 000111, then Rows 4-8 have 000113, then there will be a new page starting at column 4.

This is the code for this portion of my macro:

 Dim FromSheet As Worksheet
    Dim LastRow As Long
    Dim MyValue As Variant
    Dim EndPage As Range
    Set FromSheet = ActiveSheet
    Set FromRow = 2
    '- main loop
        MyValue = FromSheet.Cells(FromRow, 2).Value
        ToRow = 2
        '- set page breaks
        While FromSheet.Cells(FromRow, 2).Value = MyValue
            FromRow = FromRow + 1
        Set EndPage = FromSheet.Cells(FromRow, 1)
        FromSheet.HPageBreaks.Add Before:=EndPage
    Loop While FromRow <= LastRow
When I run it, I get an error, highlighting the 2 from 'Set FromRow = 2' , and the error says "Object Required".

Can anyone help?

Thank you!

Hi all,

How do I set a page break within a worksheet so that it does not change when I create another page break? I have a worksheet where the data is dynamic and I need to set page breaks based on how much data is in the sheet (so it fits on a printed sheet). I set the first page break and things are fine, but when I set the next break, the first break moves to a row where I don't want it... how do I get it to remain in the same row?


I've tried but can't figure it out.

I would like to incorporate a Page Break in my macro at every instance of Crew * Total so when I print the sheet it will give me a sheet for eack Crew.

The sheet looks something like this, but of course each Crew Total block of information will change. In other words the range (rows) for each Crew may be greater or less each time.

And then the other problem is, one Crew may only have two members as shown, but another Crew may have five or six. So vertically the page break will be different.



Excel tables to the web >> Excel Jeanie HTML 4

Any Ideas


How do I set page breaks by number of rows in Excel. For example I want every
page to have 44 rows each row at a height of 15. I have set a 50 page
spreadsheet using the auto page breaks and it keeps moving on me - driving me

Any advice gratefully received

Dennis Vaughan

Does anyone know how to write code to set page breaks every 4th column, or in certain intervals. For rows I have a simple .FitToPagesTall = 1, but I'm looking for something more dynamic for the columns. Thanks in advance.


I have a problem concerning deleting automatic page breaks in excel
2003. I found this code in a previous discussion:

Sub DeleteHPageBreaks()
Dim pb As HPageBreak
Dim lCount As Long
For lCount = Sheet1.HPageBreaks.Count To 1 Step -1
Set pb = Sheet1.HPageBreaks(lCount)
If pb.Type = xlPageBreakAutomatic Then pb.Delete
Next lCount
End Sub

The code seems to work fine in previous excel versions, but not in
2003. Does anyone know how to solve this in excel 2003?

I would appreciate your help!


I am hoping that someone can help me with a problem I am having with
the page breaks on Excel 2003. My macro builds a report and copies the
report to a set range on a spreadsheet. Based on the customer, the
number of similar reports generated will vary. As I build the reports
and set manual page breaks, for the first two reports everything works
fine. Starting with the third iteration, an automatic pagebreak is
inserted into the print area and then all subsequent reports are
offset. When it comes time to print the report, the print area is
affected and then forces me to manually set page breaks in order to
keep track of the number of pages.

How can I build multiple reports and force the page breaks to fall
where I want them?

Hello all! First i want to say this forum has been a great tool with my new job and helping me compile codes that makes our department reporting run much faster.

Now what i am trying to do is count cells with data in between page breaks in a given column, and have that number show in a cell that will always be above the page break in a given row.


clm clm clm clm

data data data data

data data data data
date data data data

data data data data

data data data data
i have a macro that is currently sorting the data and adding rows and page breaks, now if it could only count cells with data in x column and input that number into column x right above the pag break.

Any help is appreciated!

Is there a way to set page breaks at the location where a cell changes value. What I have is a sheet with a list of contracts by year and I want to print it them out on seperate sheets. Some years would be multiple sheets other would be only partial sheets.

Most Searched Demo: Take control of page breaks in your Word document

Watch these demos to find out how to manage page-break options and achieve the best pagination for your document.

How can you set page breaks through VBA? I need to set a page break every 65 rows and for it to stop at a named range ("Bottom")at the bottom of the report. Thanks for any assistance as I am new to VBA....

I have a Excell document with set page breaks. Sometimes I sort differently
and want to print that without the page breaks. How can I do that without
having to remove my previous page breaks and then inserting them again?


I have Office 2000 Professional installed. I am unable to move page breaks in
Page Break Preview. I have no idea why this is a problem now. I had always
been able to adjust the page breaks using Page Break Preview. Why am I having
this problem now? I would appreciate your thoughts on this subject.

P.S. I'm also having a problem with Word in the same suite. It unexpectedly
quits when I open a spanish language document. Anyone have any clues why?

I suddenly can't adjust my page breaks in any Excel spreadsheet while in Page
Break Preview. Is there some option to turn it on and off?

Is there a way to set page breaks when printing the vb code? I would like to
print out each sub on a different page so that they are not cut off. Any

How can I disable automatic page breaks in Excell 2003

I have a worksheet that is setup fit to page. Right now it has about 1000 rows of data with about 200 sections that can have anywhere from 3 to 6 rows allotted per section.

The problem is when I print, invariably some sections end up page breaking in the middle of a section.

Is there any way in excel to insure that certain rows of data will never page break. I cannot insert manual breaks as the size of the sections and the size of the data will continually change.

I know in word there is something called keep lines together that prevents paragraphs from page breaking. I need something similar in excel.

Any help is greatly appreciated.

How do I remove a manual page break in Excel with Office 2003?

I was hoping to adjust the below macro to run without the search function. Currently it is set to bring up a query box and ask for the search sting. I would like it to auto run and insert a page break every time it finds the word "Student" without the query box.

    Dim c               As Range 
    Dim FirstAddress    As String 
    Dim Search          As String 
    Dim Prompt          As String 
    Dim Title           As String 
    Prompt = "What do you want to search for?" 
    Title = "Search Term Input" 
    Search = InputBox(Prompt, Title) 
    If Search = "" Then 
        Exit Sub 
    End If 
    With ActiveSheet.UsedRange 
        Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) 
        If Not c Is Nothing Then 
            FirstAddress = c.Address 
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing And c.Address  FirstAddress 
        End If 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


No luck finding an answer? You could always try Google.