Function to auto insert page breaks

alexv

New Member
Joined
Jun 26, 2006
Messages
27
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Manual Page Breaks

alexv,

I had to make a few assumptions. The first is that Column B has an equation that says "=SUM". This won't work if it doesn't.

Next, I'm assuming that your sheet has many names, not just one--so the code will go through the entire sheet...if the sheet only has one name, this would be a much different solution.

Additionally, how you have your page set up (landscape vs portrait) will affect the final look of the product. As well, if the columns B-U overlap the page width, the code below won't take that into account.

And so, with all that being said, please try this if you think it will help:

Code:
Sub ManualPageBreak()

For x = Range("B65000").End(xlUp).Row To 9 Step -1

    If Left(Range("B" & x).FormulaR1C1, 4) = "=SUM" Then
    Range("B" & x + 1).Select
    ActiveWindow.SmallScroll Down:=3
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    End If
    
Next x
End Sub

In the future, when you are considering having an action happen only under certain conditions, please try to give as much detail about the conditions (cells, formulas, locations, effects, etc.)

I hope at least this will get you going in the direction you want.

Cheers,
Geoff
 
Upvote 0
Manual Page Breaks

This is how the sheet is set up:

row 1 -8 are just headings

Example of next rows:

A1: Mouse, Mickey
B1 - U1: Data

A2: Mouse, Mickey
B2 - U1: Data

A3: Mouse, Mickey Sum
B3 - U3: blanks except for R3 that has a sum of all items for Mouse, Mickey

This would repeat for each employee's details. An employee may only have 1 detail line, or can have multiple detail lines. So yes, the file contains more than one name. The sheet will be printed to landscape with all columns fitting on one page, but rows can fit on multiple pages.

Does this make sense?
 
Upvote 0
How do you get "A3: Mouse, Mickey Sum"? What is in the A3 cell itself?

What sets this cell apart from all other cells? Once we get that, we can get code for it.

Cheers,
Geoff
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top