Free Microsoft Excel 2013 Quick Reference

Inserting Page Breaks on relative Rows based on Data

Each week I format a report that I receive that lists every product a particular employee closed. Its a list of each employee, the product and date. I'll have about 20-30 lines for each employee. I can easily sort by the employee name to have a nice list.

From here what I do is use a forumla to mark where each employee's name changes, then filter by that changed line and insert page breaks for each of these lines.

Is there a way to macro this to save me time, having 270 employees takes alot of time still, because thats 270 manually inserted page breaks.


Post your answer or comment

comments powered by Disqus
I have an excel sheet with 30,000 rows of data which i need to insert a page break every 100th row- any ideas for a vba solution?

cheers

I have a very large excel document and I need to insert a page break, say
after a fixed number of rows eg 6 rows. To insert page break manually it will
take hours. Can anybody help ?

Hello all ,
I found this code from Ron De Bruin to insert page breaks depending on the number of rows.
Works great , but i need to adjust it for my purposes.
I'm helping someone else on this forum for this problem.
In a sheet i define the rows(1:15) to be repeated at the top for printing.
I'd like to insert page breaks from row 16 every RW rows ( RW = define rows in the code)
Has someone have a solution for this.
Many thanks

Sub Insert_PageBreaks()
    Dim Lastrow As Long
    Dim Row_Index As Long
    Dim RW As Long

    'How many rows do you want between each page break
    RW = 20
    
    With ActiveSheet
        'Remove all PageBreaks
        .ResetAllPageBreaks
        
        'Search for the last row with data in Column A
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        
        For Row_Index = RW + 1 To Lastrow Step RW
            .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
        Next
    End With
End Sub


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.


	VB:
	
 InsertPageBreaks() 
     
     '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 
    Else 
         '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 
                    Err.Clear 
                    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

Thanks,

SHEKK

This is driving me insane.
I have a single worksheet that prints to about six pages and has headings set out on various rows throughout it .. Is there any way of ensuring when I print the worksheet that the headings do not end up as the last line of any individual pages.
I do not want to insert page breaks as a lot of free text can be entered on this worksheet and that would cause a lot of unwanted white space when printing. I am looking for a VBA fix that will check the last line of data before the end of each page when the worksheet is printed
Any help gratefully received.

I need to be able to programatically set page breaks for an Excel Worksheet.
The following code divides the total number of rows by the the number of people (an input value) so that each page gets the same number of rows.
Private Sub CommandButton1_Click()    
    Dim rng As Range, pageRows#, totalRows#, n#
    Dim numReps As Long    
    Set rng = Range("DATA")
    numReps = InputBox("How many Collectors?", "Set Page Breaks")         
    totalRows = rng.Rows.Count
    pageRows = Int(totalRows / numReps)
    n = 1
    Cells.PageBreak = xlNone
    Do Until n > totalRows
    	n = n + pageRows
    	ActiveSheet.HPageBreaks.Add Before:=Rows(n)
    Loop
End Sub
What I need to do now is instead of evenly dividing the number of rows, I need to look at the number of unique values in column E and and divide those evenly among the number of people (which will be input).
So if there are 200 unique values in column E, 350 total rows of data, and the user input 15 as the number of people, then
I need to insert page breaks after every 13th unique value in column E.
Any ideas on how to accomplish this?

Perhaps not an idiot but, I've hit a brick wall trying to insert a page
break with code.

The problem

300 plus rows of data requiring splitting when data in a certain row chages.
On change, insert new line, create a heading summarising data below, format
the heading and insert a page break

Kept getting getting no result trying different method with HPageBreak.
Examples

ActiveSheet.HPageBreaks.Add Before:=Activecell.offset(1)

Selection.offset(1).select

ActiveSheet.HPageBreaks.Add Before:=Activecell

Selection.offset(-1).select

No problem thinks I. Just record a macro and copy code from there.

Steps:

start recorder:

Clear all breaks

Select cell

Insert page break

stop recorder

The recorder generated code but, in pagebreak view I could not see any
break inserted. In print preview, ditto. Started gettiing annoyed by now.
Tried manually dragging breaks into pagebreak view. Voila. Lines appear.
However, the breaks don't appear in print preview.

Just tested code at home. You guessed it. Breaks appear.

What may be causing the lack of breaks in Excel at work? Page setup,
perhaps?

What's the best method to insert a break away from the selected cell?

Hello

We have a big worksheet of data, with the first column as a date... it is sorted iby this from earliest to latest. Each date has a few hundred rows.

Is is possible to make Excel print this with an automatic page break after each date? We have to file the report on a day by day basis... currently the only way I can think of is to manully eyebal the data and Insert>Page Break at the appropriate places.

Any smarter way of doing this?

cheers
G06.

Does anyone know any VBA code that can be used to set a page break on the fly? I am designing a report with multiple worksheets, containing paired bar charts and a corresponding data row. If 'sheet1' has 2 paired bar charts with each set having it's own data row, there is no problem with the page breaking 'normally.' The problem arises if there are too many charts/data rows for 1 page, and excel breaks the page automatically. I would like it to break so that an entire pair and it's corresponding data is on one page, vs half of the data on one page and the other half on the next page.

I run reports a few times a month and would like to add a function to auto insert page break. The spreadsheet looks something like this:

Row 1 - 8 are headings

Row A9= last name, first name (columns B-U have data)
Row A10= last name, first name (columns B-U have data)
Row A11 = last name, first name SUM

So we may have 1 line of detail for 1000 lines of detail for an employee and then a sum underneath his name. I want to insert a page break after the sum information.

Any help would be appreciated.

Is anyone aware of a problem with Excel 2003 and inserting page breaks? When
we choose Insert | Page Break from the menu it seems to add it (you can right
click and it says to remove page break) but when you print or print preview
it seems to ignore those breaks. We can drag the blue line to rearrange the
pages but it takes significantly longer to manage this spreadsheet (which is
generated weekly) -- my guess is because Excel is redoing the entire sheet
based on the drag/drop page.

The customer indicates this did work (a month or so ago) and recently
stopped...

Thoughts/suggestions?
Thanks!
Michelle

Excel: how to insert page breaks between all the rows at once?

Hi there,

Is there a method to put page breaks in the used range every 50 rows but
exclude any hidden rows in the count of rows.

Example: a sheet with 200 rows of data and row 25 to 39 and 50 to 74 are
hidden. The first page break should then appear at row 90 and the second one
at row 140 etc.

Any help much appreciated.
--
Regards,

Martin

I have attached an example of the raw data and the pivot table I would like to print. We need to print out each company's invoice details and attach them to the check. We only need to print out the information in the pivot table. The only way I know how to print each company on a different sheet is to insert page breaks. There are over 400 lines of data on the actual pivot table. This would take ages for me to manually insert the breaks. Is there a way to write a macro?

Thanks,
Jennifer

Am making a last effort to figure this one out.

This is what I would like to happen:

I have set up line between all the columns so automatic print area wont
work so....

Would like a page break on every 25 lines, and only want "whole" pages
that are actually in use to print. All entries will be in column A.
I.e. if there is an entry in A26, want page breaks on 25 and 50 and a
horizontal line to be added above the page break (between 24 and 25)

THEN

I would like "page of pages" to appear in cell C5 based to above
criteria. Page info need only be updated when user switches to look at
print preview.

Does this make sense? Where do I put this macro? Would like info to
update when user saves, prints, saves to PDF or views print preview.
Would like to avoid have to insert a button becasue my colleagues tend
not to use/understand them.

Is this possible?
Would be very very grateful for help =)

Nina

Excel spreadsheetwith many columns. Column A has customer names, other
columns have product, price, pak, etc. Sorted data by column A.
I want to print seperate pages for each customer. So I want to insert a page
break above each new customer. So if A1 and A2 differ, I wish to insert page
break above A2. Normally, I would go to cell A2 and alt-I,B. But I have
hundreds of lines with dozens of unique customers. So I'm looking to insert a
new column A (customers will then move to column B, of course), and formulate
in A2: (@if A2=A1,"",INSERT PAGE BREAK) . Then copy it to the entire column!
Does anyone how to do this? Thanks!

I have an EXCEL 2003 file containing about 7000 addresses like this:
FIRST LAST NUMBER STREET
Mary Smith 123 Main
Tom Jones 789 Maple St.
Fred Clark 456 Main St.

I want to insert page breaks so that when I print it, each street will be
together on the same page. That is, Smith and Clark will be on the same page
because they both live on Main St. and Jones will be another page because he
lives on Maple St.

I would sort the worksheet by Street, then by number. There are more than
one hundred street names. I would rather not have to manually insert each
page break at a new street name. Is there a way to get EXCEL to
automatically make page breaks each time the street name changes? I will
appreciate advice.

In Excel (2007), I am looking for a way to set a page break into empty-spaced cells between customer information cards. Each 'card' is separated by 3 or 4 empty rows and instead of manually going down 100-150 pages and setting a page break between cards, it would be great to have a way to get the page breaks between the cards with a command. The 'cards' are not the same length, but they are all the same width. If anyone has any suggestions for me to automatically insert page breaks where there are empty rows, I'd sure appreciate it.
There is an example attached that has two of the 'cards'. There are over 100 cards to a spreadsheet and I'd like to separate each one.

I tried Gord D.'s "clunker".
Couldn't get it to work, even with help from the group.
Then I found the following, apparently originally from Frank Kabel.
Works great, EXCEPT, it also puts a page break under the header.
Can someone tell me how to get the following to ignore header rows.
If I can specify (within the module), the number of header rows,
this macro would be very versatile. (for many people)
Just specify how many header rows there are,
and which column is to be searched......
and Bob's your uncle.

Sub AAAInsertBreak()
' AAAInsertBreak Macro
' Insert Page Break after each change of
' Data in Column B
' From Frank Kabel, Germany

' I added the following reset
ActiveSheet.ResetAllPageBreaks

Dim lastrow As Long
Dim row_index As Long

'All the "B"'s were "A"'s, originally

lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "B").Value <> _
Cells(row_index + 1, "B").Value Then
ActiveSheet.HPageBreaks.Add Before:= _
Cells(row_index + 1, "B")
End If
Next

'I added the Message Box
MsgBox "COMPLETE!"

End Sub

I have RMB - Reset All Page Breaks.
Then I select a ROW, RMB - Insert Page Break. They page break is not inserted.
I also tried to add a vertical/column page break with no luck.

I tried this from the pull down menus as well (Insert - Page Break).

Any idea what could be wrong?

Using Office Professional Edition 2003

Thanks,
Sean

Hi

I have a spreadsheet which I need to print out and distribute.

There are 5000 rows and the data starts in row 10. Rows 1 -9 contain some general information that needs to be included at the top of every printed page

Column A contains a location and there needs to be a page break at every change in data:

LOC 1
LOC 1
LOC 1
PAGE BREAK
LOC 2
LOC 2
LOC 2
PAGE BREAK
LOC 3
LOC 3

There are approximately 800 different locations.

Can anyone provide any pointers?

Thanks
Andy

I need to insert a page break when an if statement is true, but if statement is false, do not insert page break?
Thanks in advance

How do I keep someone from changing page breaks on a sheet that is protected.

I have a worksheet that in column "S" I have written a simple equation
that checks column "R" to see if a value is repeated. When it comes to
an instance where the cell in column "R" does not repeat, it inputs
"Insert Page Break" in the affected cell in column "S".

Is there a way to automatically insert a page break at the cell that
"Insert Page Break" occurs and continue down the worksheet until it
finds the same instruction again?

You can see an example attached.

Chris Nelson

+-------------------------------------------------------------------+
|Filename: test1.txt |
|Download: http://www.excelforum.com/attachment.php?postid=4108 |
+-------------------------------------------------------------------+

--
chrisnelsonusa1
------------------------------------------------------------------------
chrisnelsonusa1's Profile: http://www.excelforum.com/member.php...o&userid=29526
View this thread: http://www.excelforum.com/showthread...hreadid=492279


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