MrExcel Publishing
Your One Stop for Excel Tips & Solutions

page break formatting help

Posted by gene rodriguez on December 25, 2001 6:12 PM

I have a web based report that prints out records of data. There is a link that I can click on to convert the report to an excel spreadsheet.

The one problem I have is that I always have to go back into the spreadsheet and adjust the page breaks. Is their a way of having excel identify the records and page break only at the end of a record? Records vary in length anywhere from 5 to 8 rows and are separated by borders.

Can I acheive this by a macro or some other method?

Posted by Joe Was on December 26, 2001 7:40 AM

1st a non-code trick;
on page setup select fit to pages and select one page wide, then erase any value from pages long. Excel will then determine how many pages it will need on its own.


Use the code:

Activesheet.PageSetup.FitToPagesWide = 1
Activesheet.PageSetup.FitToPagesTall = False

If your sheet has hard coded pagebreaks or not this will give you automatic pages. JSW

Posted by gene rodriguez on December 26, 2001 9:17 PM

I understand how to get it do print out in several pages. My problem is that it cuts off records in the middle during a page break. For instance let's say the first page breaks after row 30. My record may start on Row 28 with a customer name, Row 29 has Address 1, Row 30 Address 2, then row 31 has city and state, but is on the next page. Right now I manually move the page breaks to the end of each record (which has a border on it), how can I do this automatically?

Posted by Joe Was on December 27, 2001 10:33 AM

Convert your data to a Pivot Table and format with Layout-select: Insert page break after each item.
This will add each record to its own page.


add a blank row to the top of each record block then off to the right of your sheet in a column add a flag for each data row. Then build a print macro which counts the number of rows you want on a page. Then "xlup" to the next blank row and put a page break. Continue for each page, then print the document, then delete all page breaks. A macro like this is the only way I know of to keep odd numbered rows of data together when printing. JSW

Posted by gene rodriguez on December 27, 2001 6:15 PM

Sounds like the time time consuming thing would be to just continue to manually adjust the page breaks before printing. I am trying to avoid having to do the manual page breaks.