How to put the page numbering on the cell

[chowsc]

How to put the page numbering on the cell not on the header or footer

and when i print out, the number change

Many Thanks

 

[Troncoso]

The program you are using would be a good start.

 

[chowsc]

what do you mean?

 

[Okedokey]

Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code in.

On the worksheet in the cell you want the page number to appear type the formula:

=Pagenumber()
Public Function PageNumber( _
Optional ByRef rng As Excel.Range) As Variant
Dim pbHorizontal As HPageBreak
Dim pbVertical As VPageBreak
Dim nHorizontalPageBreaks As Long
Dim nVerticalPageBreaks As Long
Dim nPageNumber As Long

On Error GoTo ErrHandler
Application.Volatile
If rng Is Nothing Then _
Set rng = Application.Caller
With rng
If .Parent.PageSetup.Order = xlDownThenOver Then
nHorizontalPageBreaks = .Parent.HPageBreaks.Count + 1
nVerticalPageBreaks = 1
Else
nHorizontalPageBreaks = 1
nVerticalPageBreaks = .Parent.VPageBreaks.Count + 1
End If
nPageNumber = 1
For Each pbHorizontal In .Parent.HPageBreaks
If pbHorizontal.Location.Row > .Row Then Exit For
nPageNumber = nPageNumber + nVerticalPageBreaks
Next pbHorizontal
For Each pbVertical In .Parent.VPageBreaks
If pbVertical.Location.Column > .Column Then Exit For
nPageNumber = nPageNumber + nHorizontalPageBreaks
Next pbVertical
End With
PageNumber = nPageNumber
ResumeHere:
Exit Function
ErrHandler:
'Could use much more error handling...!
PageNumber = CVErr(xlErrRef)
Resume ResumeHere
End Function