Mehedi Hassan

Hello Bart,
Thank you for reaching out. Your problem has a very simple solution. Instead of writing the code in a Sub Procedure, create an Event, Worksheet_SelectionChange in each of your Worksheet and simply paste your code.

Do the same thing in every worksheet except the Index worksheet and modify the code as per your requirements. In this way, you don’t have to run the code every time.
Here’s the modified code:

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim mVCount As Integer
    Dim mHCount As Integer
    Dim mVBreak As VPageBreak
    Dim mHBreak As HPageBreak
    Dim mNumPage As Integer
    mHCount = 1
    mVCount = 1
    If ActiveSheet.PageSetup.Order = xlDownThenOver Then
        mHCount = ActiveSheet.HPageBreaks.Count + 1
    Else
        mVCount = ActiveSheet.VPageBreaks.Count + 1
    End If
    mNumPage = 1
    For Each mVBreak In ActiveSheet.VPageBreaks
        If mVBreak.Location.Column > ActiveCell.Column Then Exit For
        mNumPage = mNumPage + mHCount
    Next
    For Each mHBreak In ActiveSheet.HPageBreaks
        If mHBreak.Location.Row > ActiveCell.Row Then Exit For
        mNumPage = mNumPage + mVCount
    Next
    ThisWorkbook.Worksheets("Worksheet 1").Range("I2").Value = "Page " & mNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    ThisWorkbook.Worksheets("Worksheet 3").Range("C5").Value = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub

Here is the result:

Hopefully, this will solve your problem.

Regards
Hassan Shuvo || Exceldemy