This can be done in VBA, but is a bit involved, mainly because the Excel object model does not provide properties relating to page numbering, such as the total number of pages that a worksheet will print, etc. Thus your code has to figure out the number of pages each worksheet will print, and add them up to get the total number of pages for all worksheets. To count the pages you have to use the number of pagebreaks. I believe that the number of pages in a worksheet is the (number of horizontal page breaks + 1) x (number of vertical page breaks + 1).
Once you have the total count, I believe your code will need to print each page, one at a time, putting the numbers in the headers individually. If you have a lot of other stuff in the header in addition to the page number such that finding where the page number should be written could be a problem, simply put a text string like
in the header. Then have your VBA code search and replace the
string with the computed page number, and with the computed total page count.
I hope this at least gets you started.