VBA Print & Page numbers

Hi all,

I'm having a real tough time at the minute trying to get the correct page numbers to display on an excel file that is ultimately printed as a .pdf document.

I have a workbook containing 30 sheets that are printed into a .pdf document. Within the document I have some code that prints each sheet seperately and places a page number on the page.

Here's the code:

Please Login or Register  to view this content.

The problem I have is when one of the sheets is displayed over two or more pages. The code puts the same page number on each of the sheets. I've been searching high and low to find something which follows this logic;

Please Login or Register  to view this content.

Anyone got any idea's on how I can overcome this?

Thanks,
Lee