I do the same thing and it works. The only one thing not good is the font size changes with different Excel print area page set ups. I print Excel book in PowerPoint also, those numbers are generated by PowerPoint and thay all are same size and look better.
Actually there is another issue, when an Excel sheet data region is large, one sheet will break into multiple pages, so the page number may go out of order. I need to find a way to count page number if page splits.
As the issue has become a bit different than the original thread you started, I've split this into a new thread. Bascially we're looking for a way to control the page numbering via VBA now.
The only ways I can see to do this is are to use VBA to:
Change the header/footer on the sheets before you print them to the correct page number
Actually put the page numbers in the worksheet. (Personally I'd stay well away from this as it would be a pain to do.)
I'll let you record a macro to set a page header/footer to get the code to set page numbers. It will be the page counting that's the issue for you. You can get the count of pages for a worksheet using the following code:
Dim ws as Worksheet
Dim lPages as Long
Set ws = Activesheet
lPages = Activesheet.PageSetup.Pages.Count
Unfortunately though, I don't believe you can set the initial page count using a variable, so if you're printing multiple sheets, you may have to do them individually, setting the page number for each page. The kicker is that the PageSetup stuff is unbelievably S-L-O-W. So you may suffer in that case.
Curious... can you chapterize it? What I mean is set up your numbering so that it uses worksheet counting, but is prefaced with an index?
i.e. 10-x, 20-x, 30-x
x would be the variable page number per the headers and footers, but you could manually declare the preface in the order you wanted to see it? (This is the way we index working papers in accounting to avoid having to renumber the whol file every time we add more support pages.)