Sub test()
'Нумерация страниц, страница ставится в последней строке странице и в средней колонке
Dim printRng As Range
Dim columnToPage As Long
Dim countHPageBreak As Long, countVPageBreak As Long
Dim hPB As HPageBreak, vPB As VPageBreak
Dim pageLastRow() As Variant, pageColumn() As Variant
Dim x As Long
Dim i As Long, j As Long, ihPB As Long, jvPB As Long
    
    columnToPage =  5  ' в какой колонке ставить номер

  With ActiveSheet
    
    If .PageSetup.PrintArea <> "" Then
        Set printRng = .Range(.PageSetup.PrintArea)
    Else
        Set printRng = .UsedRange
    End If
    
    countHPageBreak = .HPageBreaks.Count
    ReDim pageLastRow(countHPageBreak) As Variant
 
    i =  0 
    For Each hPB In .HPageBreaks
     pageLastRow(i) = hPB.Location.Row -  1 
     i = i +  1 
    Next hPB
    pageLastRow(countHPageBreak) = printRng.Rows.Count + printRng.Row -  1 
        
    countVPageBreak = .VPageBreaks.Count
    ReDim pageColumn(countVPageBreak) As Variant
    
    j =  0 
    x =  1 
    For Each vPB In .VPageBreaks
     pageColumn(j) = Fix((x + vPB.Location.Column -  1 ) /  2 )
     j = j +  1 
     x = vPB.Location.Column
    Next vPB
    pageColumn(countVPageBreak) = Fix((x + printRng.Columns.Count + printRng.Column -  1 ) /  2 )

    
    For jvPB =  0  To countVPageBreak
        
        For ihPB =  0  To countHPageBreak
         .Cells(pageLastRow(ihPB), pageColumn(jvPB)).Value _
            = "Cтраница " & (countHPageBreak +  1 ) * jvPB + ihPB +  1  & " из " & (countHPageBreak +  1 ) * (countVPageBreak +  1 )
        Next ihPB
    
    Next jvPB
    
  End With
End Sub