PDA

View Full Version : VBA Page Breaks in Excel



giraffe3289
07-06-2008, 07:21 PM
Hi,

I wrote a macro in excel recently but am still having difficultly with the page breaks. My macro opens various excel files and copies the report worksheet of each file as a picture. It then pastes each report worksheet into a seperate worksheet of a new excel file so that all the worksheets are in one file and can be printed all at once. The files that the macro is pulling the reports from all have page breaks set at the correct points, but these page breaks are not imported when the report is copied. I have done my research and have tried various bits of code, but none seem to be working. Most recently I tried the following:

'set manual page breaks
ActiveSheet.Rows.PageBreak = xlNone
ActiveSheet.DisplayPageBreaks = False
ActiveSheet.Rows(49).PageBreak = xlPageBreakManual

However, when I run this code, the macro just places a dark horizontal line above row 49 (where I designated the page break) and continues to break a few lines down right through the middle of a graph. Before I inserted this code, I have a few lines that designate the print area and tell it to fit to 2 pages long and 1 page wide. These lines work and I'm 100% sure that the sheet I want active is active. I'm not sure why it's ignoring my page break code, but I'd really appreciate some help. Thanks!

RonMcK
07-07-2008, 09:38 AM
You might try turning on marco recorder, inserting a page break (or 2 or 3), turning the recorder off, and viewing the code it created. This will show you how Excel does page breaks. Splice that into your macro and adjust as needed.

Regards,