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:

Sub ExportOutput()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim outputJob As PDFCreator.clsPDFCreator
Dim sThisBook As String
Dim sThisSheet As String
Dim sOutputFile As String
Dim sOutputPath As String
Dim lOutputFormat As Long
Dim lSheetCount As Long
Dim sTargetSheet As String
Dim pageNumber As Long
Dim iHpBreaks As Integer
Dim iVBreaks As Integer
Dim iTotPages As Integer

sThisBook = ActiveWorkbook.Name
sThisSheet = ActiveSheet.Name

Windows(sThisBook).Activate
Sheets(sThisSheet).Select
Range("A1").Select

sOutputFile = Range("TargetFilename").Text
sOutputPath = Range("TargetFilepath").Text
lOutputFormat = Application.VLookup(Range("OutputType").Text, Range("OutputLookup"), 3, 0)
lSheetCount = Range("SheetsToPrint").Cells.Count
pageNumber = 1

Set outputJob = Nothing

Set outputJob = New PDFCreator.clsPDFCreator

With outputJob

    If .cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "PrtPDFCreator"
    Exit Sub

    End If

    .cOption("UseAutosave") = 1
    .cOption("UseAutosaveDirectory") = 1
    .cOption("AutosaveDirectory") = sOutputPath
    .cOption("AutosaveFilename") = sOutputFile
    .cOption("AutosaveFormat") = lOutputFormat
    .cClearCache

End With

For Each cell In Range("SheetsToPrint")

sTargetSheet = cell.Text


iHpBreaks = Sheets(sTargetSheet).HPageBreaks.Count + 1
iVBreaks = Sheets(sTargetSheet).VPageBreaks.Count + 1
iTotPages = iHpBreaks * iVBreaks

If Sheets("Control").Range("C" & cell.Row).Text = "x" Then

Application.Sheets(sTargetSheet).PageSetup.RightFooter = "&""Arial"" &11" & "Page " & pageNumber
Application.Sheets(sTargetSheet).PageSetup.LeftFooter = "&""Arial"" &11" & sOutputFile

Else

Application.Sheets(sTargetSheet).PageSetup.RightFooter = "&""Arial"" &15" & "Page " & pageNumber
Application.Sheets(sTargetSheet).PageSetup.LeftFooter = "&""Arial"" &15" & sOutputFile

End If

Application.Sheets(sTargetSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
Application.Wait Now + TimeValue("0:0:3")

Application.Sheets(sTargetSheet).PageSetup.RightFooter = ""
Application.Sheets(sTargetSheet).PageSetup.LeftFooter = ""

pageNumber = pageNumber + iTotPages

Next cell

Do Until outputJob.cCountOfPrintjobs = lSheetCount
    DoEvents
Loop

With outputJob
    .cCombineAll
    .cPrinterStop = False
End With

Do Until outputJob.cCountOfPrintjobs = 0
    DoEvents
Loop

Application.Wait Now + TimeValue("0:0:55")

With outputJob
    .cPrinterStop = True
    .cClose
End With

Set outputJob = Nothing

Windows(sThisBook).Activate
Sheets(sThisSheet).Select
Range("A1").Select

End Sub

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;

If pagecount of ("sheeta") = 2 Then

Print page 1 of 2 (placing page number on the sheet)

&

Print page 2 of 2 (placing page number + 1 on the sheet)

Else continue with my above code

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

Thanks,
Lee