Excel VBA Problem in Page Footer

[MacNew]

Sub FormattedPageNums()
Dim iPages As Integer
Dim J As Integer
Dim sFormat As String

sFormat = "00000"
' Get count of pages in active sheet
iPages = ExecuteExcel4Macro("Get.Document(50)")

With ActiveSheet
For J = 1 To iPages
.PageSetup.CenterFooter = Format(J, sFormat)
.PrintOut From:=J, To:=J
Next J
End With
End Sub

I obtained the above code from a site. But when i ran it, all the footer have the last page number.
e.g.If my sheet has 2 pages, footers have 00002 for all 2 pages, if i have 3 pages, footers have 0003 for all 3 pages. Could you please help me with this? (I am using Excel 2007 version)

 

[Johnny C]

All pages use the same footer, so each time you set the centrefooter you're resetting all the pages to the same footer.
Try using &[Page] , but you can't format it AFAIK