Sub demo()
If PageAddress(1) <> "" Then Range(PageAddress(1)).Select
End Sub

Sub PagesAddress()
Application.ScreenUpdating = False
Dim rgSrcX As Integer:
Dim rgSrcY As Integer: rgSrcY = 1
Dim rgTgtY As Integer
Dim rgTgt As Range
Dim paLastCell As Range
Dim rgPB As String
Dim VPBc As Integer
Dim HPBc As Integer
Dim HPB As HPageBreaks
Dim VPB As VPageBreaks

With ActiveSheet

If .PageSetup.PrintArea = "" Then Exit Sub
Set paLastCell = .Range(.PageSetup.PrintArea).Item(Range(.PageSetup.PrintArea).Cells.Count)
rgSrcX = .Range(.PageSetup.PrintArea).Item(1).Row
rgSrcY = .Range(.PageSetup.PrintArea).Item(1).Column
VPBc = .VPageBreaks.Count
HPBc = .HPageBreaks.Count
Set HPB = .HPageBreaks
Set VPB = .VPageBreaks

For j = 1 To VPBc + 1
    For i = 1 To HPBc + 1
        If j < VPBc + 1 Then rgTgtY = (VPB(j).Location.Column - 1) Else rgTgtY = paLastCell.Column
        If i = (HPBc + 1) Then
            If j = VPBc + 1 Then
                Set rgTgt = paLastCell
            Else
                Set rgTgt = .Cells(paLastCell.Row, VPB(j).Location.Column - 1)
            End If
            
            Set cl = .Range(Cells(rgSrcX, rgSrcY), rgTgt)
        Else
            If HPB(i).Location.Row <= HPB(HPBc).Location.Row Then
                Set rgTgt = .Cells(HPB(i).Location.Row - 1, rgTgtY)
                Set cl = .Range(Cells(rgSrcX, rgSrcY), rgTgt)
                rgSrcX = HPB(i).Location.Row
            End If
        End If
        
        If Not cl Is Nothing Then rgPB = rgPB & cl.Address & ","
    Next i
    rgSrcX = 1
    rgSrcX = .Range(.PageSetup.PrintArea).Item(1).Row
    If j <> VPBc + 1 Then rgSrcY = VPB(j).Location.Column
Next j

MsgBox .Range(Left$(rgPB, Len(rgPB) - 1)).Address
Debug.Print .Range(Left$(rgPB, Len(rgPB) - 1)).Address
End With

End Sub

Function PageAddress(NumPage As Integer)
Application.ScreenUpdating = False
Dim rgSrcX As Integer:
Dim rgSrcY As Integer: rgSrcY = 1
Dim rgTgtY As Integer
Dim rgTgt As Range
Dim paLastCell As Range
Dim rgPB As String
Dim VPBc As Integer
Dim HPBc As Integer
Dim HPB As HPageBreaks
Dim VPB As VPageBreaks

With ActiveSheet

If .PageSetup.PrintArea = "" Then Exit Function
Set paLastCell = .Range(.PageSetup.PrintArea).Item(Range(.PageSetup.PrintArea).Cells.Count)
rgSrcX = .Range(.PageSetup.PrintArea).Item(1).Row
rgSrcY = .Range(.PageSetup.PrintArea).Item(1).Column
VPBc = .VPageBreaks.Count
HPBc = .HPageBreaks.Count
Set HPB = .HPageBreaks
Set VPB = .VPageBreaks

For j = 1 To VPBc + 1
    For i = 1 To HPBc + 1
        If j < VPBc + 1 Then rgTgtY = (VPB(j).Location.Column - 1) Else rgTgtY = paLastCell.Column
        If i = (HPBc + 1) Then
            If j = VPBc + 1 Then
                Set rgTgt = paLastCell
            Else
                Set rgTgt = .Cells(paLastCell.Row, VPB(j).Location.Column - 1)
            End If
            
            Set cl = .Range(Cells(rgSrcX, rgSrcY), rgTgt)
        Else
            If HPB(i).Location.Row <= HPB(HPBc).Location.Row Then
                Set rgTgt = .Cells(HPB(i).Location.Row - 1, rgTgtY)
                Set cl = .Range(Cells(rgSrcX, rgSrcY), rgTgt)
                rgSrcX = HPB(i).Location.Row
            End If
        End If
        
        If Not cl Is Nothing Then rgPB = rgPB & cl.Address & ","
    Next i
    rgSrcX = 1
    rgSrcX = .Range(.PageSetup.PrintArea).Item(1).Row
    If j <> VPBc + 1 Then rgSrcY = VPB(j).Location.Column
Next j
End With
PageAddress = Range(Left$(rgPB, Len(rgPB) - 1)).Areas(NumPage).Address
End Function

Function RangePageAddress(Plage As Range)
Application.ScreenUpdating = False
Dim rgSrcX As Integer:
Dim rgSrcY As Integer: rgSrcY = 1
Dim rgTgtY As Integer
Dim rgTgt As Range
Dim paLastCell As Range
Dim rgPB As String
Dim VPBc As Integer
Dim HPBc As Integer
Dim HPB As HPageBreaks
Dim VPB As VPageBreaks

With ActiveSheet

If .PageSetup.PrintArea = "" Then Exit Function
Set paLastCell = .Range(.PageSetup.PrintArea).Item(Range(.PageSetup.PrintArea).Cells.Count)
rgSrcX = .Range(.PageSetup.PrintArea).Item(1).Row
rgSrcY = .Range(.PageSetup.PrintArea).Item(1).Column
VPBc = .VPageBreaks.Count
HPBc = .HPageBreaks.Count
Set HPB = .HPageBreaks
Set VPB = .VPageBreaks

For j = 1 To VPBc + 1
    For i = 1 To HPBc + 1
        If j < VPBc + 1 Then rgTgtY = (VPB(j).Location.Column - 1) Else rgTgtY = paLastCell.Column
        If i = (HPBc + 1) Then
            If j = VPBc + 1 Then
                Set rgTgt = paLastCell
            Else
                Set rgTgt = .Cells(paLastCell.Row, VPB(j).Location.Column - 1)
            End If
            
            Set cl = .Range(Cells(rgSrcX, rgSrcY), rgTgt)
        Else
            If HPB(i).Location.Row <= HPB(HPBc).Location.Row Then
                Set rgTgt = .Cells(HPB(i).Location.Row - 1, rgTgtY)
                Set cl = .Range(Cells(rgSrcX, rgSrcY), rgTgt)
                rgSrcX = HPB(i).Location.Row
            End If
        End If
        
        If Not cl Is Nothing Then rgPB = rgPB & cl.Address & ","
    Next i
    rgSrcX = 1
    rgSrcX = .Range(.PageSetup.PrintArea).Item(1).Row
    If j <> VPBc + 1 Then rgSrcY = VPB(j).Location.Column
Next j

End With

For Each ar In Range(Left$(rgPB, Len(rgPB) - 1)).Areas
    If Not Intersect(Plage, ar) Is Nothing Then RangePageAddress = ar.Address
Next ar

End Function

'les deux fonctions retournent une chaîne mais sont très facilement modifiables pour retourner une plage de cellules

'pour utiliser cette fonction, je recommande de contrôler que la plage en entrée soit composée d'une seule cellule
'admettons que vous donniez une plage de plusieurs cellules, la fonction retourne la dernière page avec laquelle la plage
'a une intersection ; pour retourner la première, il faudrait rajouter une instruction exit function.


'attention si vous voulez appliquer cette fonction à des pages d'une autre feuille, il faut faire quelques modifs
'en contrôlant la feuille parente de la plage, vous pouvez ainsi remplacer
'with activesheet par with worksheets(plage.parent.name)