Selecting a Page (not a sheet) using VBA.

[stuartjbray]

Hi, Here's a tricky one...

I want to be able to select a specific page in a workbook, and copy it
to another workbook. For example: if sheet 1 prints out over 3 pages, I
need the vba to select the second page of this.

I can use VBA to select manually added pagebreaks, by referring to the
vpagebreaks and hpagebreaks collections of the sheet, but this does NOT
reference automatically added page breaks. This is probably because
auto-pagebreaks have to be recalculated (re-paginated) every time you
add or remove lines.

I also tried looking at things like sheet.pagesetup.printarea, but no
luck.

Is it possible to do something like: thisworkbook.printout() and use
the print to file, to export the pages as a seperate workbook?

Failing this, I shall go tell my client that it can't be done and blame
excel.

 

[Ron de Bruin]

You can adapt the code in this example maybe
http://www.rondebruin.nl/hpagebreaks.htm

 

[stuartjbray]

Cheers Ron, that solves it.

In my initial testing I had assumed that hpagebreaks only included
manually added breaks, not auto. But obviously that is not the case.