Lister des onglets

Option Explicit


Sub test()
Dim Trouve As Range, Expression As Variant
Dim Adr As String


Expression = Application.InputBox( _
Prompt:="Ville recherchée?", Type:=2)
If Expression = False Then Exit Sub
If Expression = "" Then Exit Sub


With ActiveSheet
Adr = .UsedRange.Address
With .UsedRange
Set Trouve = .Find(What:=Expression, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
End With
If Not Trouve Is Nothing Then
Trouve.Select 'où le traitement que tu veux appliquer
Else
MsgBox "Pas trouvé l'expression : " & Expression
End If
End With
End Sub

Sub test()
Dim Trouve As Range, Expression As Variant
Dim Adr As String
Dim sh As Worksheet

Expression = Application.InputBox( _
Prompt:="Ville recherchée?", Type:=2)
If Expression = False Then Exit Sub
If Expression = "" Then Exit Sub

For Each sh In Sheets
  With sh
    Adr = .UsedRange.Address
    With .UsedRange
      Set Trouve = .Find(What:=Expression, LookIn:=xlValues, _
      LookAt:=xlWhole, MatchCase:=False)
    End With
    If Not Trouve Is Nothing Then
      sh.Select
      Trouve.Select 'où le traitement que tu veux appliquer
      Exit For
    End If
  End With
Next
If Trouve Is Nothing Then MsgBox "Pas trouvé l'expression : " & Expression
End Sub