Finding Page Breaks in Excel 1

[dapotter]

I have an Excel spreadsheet that contains a budget report. This report is divided into sections of varying length that will be handed out to the individuals responsible for those sections. Using VBA, I have set page breaks to divide each section. What I would like to have is a page count for each section. Though a three page section may start on page 15, I would like the printout to show 1 of 3, 2 of 3, and 3 of 3.

It doesn't look like I will be able to use the header or footer for this. Is there a method I can use (worksheet formulas or VBA) to detect where the soft page breaks exist so I can add the page count at a cell in the row immediatly before or after the break?

Thanks,
Don

 

[PeterMoran]

Hi Don,

I'm no VBA expert but I would suggest you may get better results by copying each section at an appropriate stage to a separate sheet and printing the sheets separately - the old "KISS" principle!

Good Luck!

Peter Moran

 

[dapotter]

Thanks Pete,

I had considered this and will probably go that direction, but thought if there was an easy way to find the soft page breaks, someone on this forum would know the answer.

Thanks,
Don

 

[SkipVought]

Hi,

Remember that each type of application has its purposes. Where MS Word has sections, Excel does not. Excel is primarily a spreadsheet application.

Make columns for SECTION and PAGE. View your sheet in Page Break Preview. Populate the SECTION and PAGE columns according to your needs. HIDE these two columns, bur refer to them for a Page n of m format in a cell on each page.

Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[dapotter]

Thanks Skip,

The Page Break Preview did come to mind, but I'm trying to avoid manual intervention. As Peter recommended, I'll probably split the sections (as detected by my VBA code) into different worksheets, use a page m of n header on each sheet, then print the whole workbook.

I just thought that if Excel defines soft page breaks (as seen by the dashed lines in the normal view), they might provide a mechanism for me to determine where they are.

Don

 

[SkipVought]

Take a look at the HPageBreaks & VPageBreaks collections.

You ought to be able to programatically figger out where they occur. I only suggested the View to aid in the programatic solution.

Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[dapotter]

That's what I was looking for! Thanks.