Code causes Error 1004 then continues

Hi

I have some code I have created to set page breaks. Depending on a previous option, my report will be either 3,6,9,12, or 15 pages. The info on each page varies in length, but I use table headers to separate the data onto different pages so none of the tables are split into more than one page.

My problem when the code gets to a certain line, it gives me error 1004. I will hit "Debug", then I hit continue and the code finishes. I don't understand why I am getting an error on this line, and why I don't have to fix anything to get it to finish running.

Here is the code:

Please Login or Register  to view this content.

I can see the cell its refering to is valid, cell(89,1).
Any ideas?

Thanks

Pagebreaks are a collection of manual and automatic. You can set manual pagebreaks, but Excel sets automatic ones based on all the things you expect -- paper size, margins, row heights, print area, ...

You shouldn't be trying to change members of the collection; you should be adding a new one.

Se Help for the Add method as applied to HPageBreak.

Ok, I tried switching it as follows:

Please Login or Register  to view this content.

However, excel puts an extra page break in before my first page break, which creates a page with three lines of data. This is what I was trying to avoid. When I stop and look at the worksheet, it is a dashed-line page break (not sure if that is significant).

Shouldn't all page breaks disappear when I use ResetAllPageBreaks?

All the manual page breaks go away, and then Excel recalculates the automatic ones.

Dunno why your workbook prints three rows and then breaks. Post it.

Ok, here is my original. Use the Print Report Button on Daily Icon Comparison Worksheet. You will notice the code has the error as described in my first post, but then if you hit continue it will finish the sub.

Here is an example using the other coding as you suggested. For some reason I still get extra page breaks...

I can't run that without having the other workbook. The code is still trying to change members of the hpagebreaks collection, rather than clearing the collection and starting from scratch.

If you post a minimal example (like the print workbook alone) that erroneously breaks after three lines, I'll look at it.

I have fixed the code. It should not look for other workbooks now.

I don't have a G drive, or a workbook named example1.

Ok, those are bad examples. I am going to try to create a better one.

Ok, here is an example. Why aren't the dashed line (auto page breaks) going away as I tell it where I want the page breaks?

Thanks!

Originally Posted by shg

All the manual page breaks go away, and then Excel recalculates the automatic ones.

Dunno why your workbook prints three rows and then breaks. Post it.

Is there a way to clear the automatic ones so I can start fresh with no page breaks?

Because when I use code to add manual page breaks, the automatic ones don't go away. Shouldn't they go away?

There's an automatic page break above line 53, which mean that's all the data Excel can fit on a page. When you set a pagebreak on line 75, it doesn't change the prior fact -- this is clear, right?

You can reduce the print scaling until the page break appears at or below line 75, and then set manual pagebreaks above the automatic pagebreaks.

I understand. I think my original problem could be remedied by adding this first:

Please Login or Register  to view this content.

To build on what you've said, I added this code. It takes all excel's automatic page breaks and makes them into manual page breaks. Then I can move the location of them to where I want them. This fixes my problem, though after reading your last post I think the better way to do it would be to reduce print scaling then set new page breaks.

Please Login or Register  to view this content.

Thanks for all your help shg.

This can be marked solved, thanks.

You're welcome, post back if you get stuck.

This can be marked solved, thanks.

Would you please do the honors?