Insert page number into cell

[SLW612]

Hi,
This may be complicated so I will do my best to explain clearly what I'm
trying to do.

My company prints Excel reports from our online server that can be anywhere
from 2 pages to 200 pages. The larger reports are actually compliations of
our smaller reports and they are numbered "Page 1 of 123" (and so on) at the
bottom of each page - though not in the footnote. Each page is on one
worksheet. I have designed a macro that searches for this text and inserts a
page break just below it so the pages are set up properly. The first page of
each section is almost exactly the same on each report - the difference being
only vendor name and address (equivalent to 3 cells).

However, for the large reports, I would like each individual report to be
numbered separately from the bulk of the report. I will use a 10-page
example:

Page 1 - start page, vendor A
Page 2 - Vendor A page 2
Page 3 - Vendor A Page 3
Page 4 - start page, vendor B
Page 5 - Vendor B page 2
Page 6 - Vendor B page 3
Page 7 - start page, vendor C
Page 8 - Vendor C page 2
Page 9 - Vendor C page 3
Page 10 - Vendor C page 4

Right now, when I print this report, pages are numbered 1 - 10 sequentially
and the bottom of each page reads "Page x of 10." I'd like to change this so
that page 1 will read "Page 1 of 3" followed by pages 2 and 3, and page 4
will also read "Page 1 of 3", and page 7 will read "Page 1 of 4", etc.

Also, I have named a specific cell on each start page that works correctly;
i.e. the number of named regions (with the exception of Print_Area) equals
the number of individual reports.

How can I go about this, short of manually changing cell values? I know how
to change the "Page x of xx" cell, I just need vba to calculate the x and xx!

Thanks in advance!

 

[TomPl]

You could attempt the following:

Rather than inserting Page breaks, name the range for each report then using
VBA you can print each named range. If the page numbering is in the footnote
the rest will happen automatically.
Or, using logic similar to what you are doing now to insert the page breaks,
you could select each report and print the selection. Again, with page
numbering in the footnote.
I assume that your existing code removes the lines that have the old page
numbering on them.

It is one way.