Set Page Breaks and Print Area

[redstang423]

Hi guys, I'm having a bit of difficulty with my code not working properly. It is not returning any errors, but it does not seem to have much effect on the sheet either. Here is the code (context explanation below):

Please Login or Register  to view this content.

The variable "ShadingRow" is set earlier in the code and refers to the final row that needs to be included in the print area.
The Do Loop cycles through looking for a blank space in column C since that designates the beginning of a new section (with Heading in column D). It is supposed to insert a page break before this new section.

I need this to print one page wide and have a page break after every section as I mentioned before. The code keeps it at one page wide, but cuts it off after column L no matter what. Also, the page breaks do not show despite the code finding the location and running the line of code properly. I have tried inserting various lines of code (both as a "With" grouping and individually) such as:

Please Login or Register  to view this content.

in various places with no luck. I also tried varying the format to see if that had any effect. The sheet will not always be a set number of pages tall.

Can anyone give me some advice as to why this may be happening?

Thanks!
Jeff

[Leith Ross]

Hello Jeff,

The problem with cutting off at column "L" is you have set a horizontal PageBreak (row only). To change the column from "L" to something else, you need to set a vertical PageBreak. You do this by either using the column or the VPageBreak property.

Please Login or Register  to view this content.

When using the PrintArea and PageBreaks together
If you add a page break that does not intersect the print area, then the newly-added VPageBreak object will not appear in the VPageBreaks collection for the print area. The contents of the collection may change if the print area is resized or redefined.

Sincerely,
Leith Ross

[redstang423]

Leith,

Thanks for your help. I did have the vertical break in at one point, but removed it in my attempts to get this to work. Unfortunately adding it back in did not change the problem I am having. It still breaks after column L and will not even put the remaining columns on a new page.

In what order should I be placing the breaks and print area setup? Should the print area be set and then the breaks, or the other way around? I tried both, and it didn't seem to have much of an effect on the results.

[Leith Ross]

Hello Jeff,

I don't know why you can't get past column "L". Can you post your workbook? If your are using Excel 2007, I would need a 2003 version.

Thanks,
Leith Ross

[redstang423]

To try to discover the problem, I stepped through the setting of the print area and watched what happened. The first line of my code highlights the proper area. With the second line of the code, it seemingly sets it as the print area. Here is the weird part: Columns A though L are surrounded by the typical blue lines for the print area. The range I am trying to set is surrounded by similar grey lines that appear to be print boundaries. You can manipulate them in the same way.

Once the code moves on to the third line where I select cell C3, the grey print area lines go away and the blue ones remain selecting only columns A:L. Is there one more line of code I should have in there to actually set the page breaks?

[Leith Ross]

Hello Jeff,

Try stepping through without setting the PrintArea and see what happens. Maybe setting both causes a conflict.

Sincerely,
Leith Ross

[redstang423]

I have attached a copy of the spreadsheet with all the sensitive information removed.

I am also now getting an error that I wasn't before, despite not changing any code that could possibly affect it. It will likely be taken care of if the original problem is solved.

I have discovered the source of the original problem, but I do not know how to fix it. In the original code I posted, the second line (it is found towards right near the bottom of the code in the spreadsheet) contains the ActiveCell.CurrentRegion.Address command. This is remaining at A1:L135 despite the currently selected region including through columns N. Perhaps I am using the arguement wrong, so hopefully this gives some insight.

Thanks for all your help!