extracting page number from page setup footer

The actual page number is not in the footer. Try this to see what I mean

Option Explicit

Sub x()

 With ActiveSheet.PageSetup
            Range("A1") = .LeftFooter
        End With
End Sub

You need to work with PageBreaks. You will need to adjust the difference between rows, I have used 50

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   : www.excel-it.com for more examples and Excel Consulting
' Purpose   : Returns the number of printed pages in a worksheet
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
Option Explicit

Function WorksheetCountPages(oWorksheet As Excel.Worksheet) As Long
    Dim bPageBreaksVisible As Boolean
    'Store the visible state of the page breaks
    bPageBreaksVisible = oWorksheet.DisplayPageBreaks
    WorksheetCountPages = (oWorksheet.VPageBreaks.Count + 1) * (oWorksheet.HPageBreaks.Count + 1)
    'Restore the visible state of the page breaks
    oWorksheet.DisplayPageBreaks = bPageBreaksVisible
End Function


Sub addPageNumbers()
    Dim i      As Integer
    Dim R      As Long
    R = 50

    For i = 1 To WorksheetCountPages(ActiveSheet)
        Cells(R, 1).Value = i
        R = R + 50
    Next i
End Sub