Boa tarde!!

Eu não sei se entendi completamente, mas tente adaptar, caso necessário....

Option Explicit
Private Sub AleVBA_16493()
    Dim vWhat       As Variant
    Dim wks         As Worksheet
    Dim rFind       As Range
 
    vWhat = InputBox(Prompt:="O que procura?")
    If Len(vWhat) = 0 Then Exit Sub
 
    For Each wks In Worksheets
        Set rFind = wks.Cells.Find(What:=vWhat, _
                                   LookIn:=xlValues, _
                                   LookAt:=xlPart, _
                                   MatchCase:=False, _
                                   SearchFormat:=False)
        If Not rFind Is Nothing Then
            Application.Goto rFind
            rFind.EntireColumn.Hidden = True
            Exit Sub
        End If
    Next wks
 
    MsgBox ("Não encontrado")
End Sub

Att