Pesquisar em todas as planilhas

    Option Explicit


    Public MyVal As String

    Sub SuperFIND()
    'Author:    Jerry Beaucaire
    'Date:      8/13/2010
    'Summary:  Searches all sheets for any text string, activates when found
    Dim ws      As Worksheet
    Dim vFIND  As Range

    StartOver:
    MyVal = Application.InputBox("Digite o nome", "Procura em todas as Planilha", MyVal, Type:=2)
    If MyVal = "Falso" Then Exit Sub
    On Error Resume Next

        For Each ws In Worksheets
            Set vFIND = ws.Cells.Find(MyVal, LookIn:=xlValues, LookAt:=xlPart)
            If Not vFIND Is Nothing Then
                ws.Activate
                vFIND.Select
                Set vFIND = Nothing
                Exit Sub
            End If
        Next ws
     
    MsgBox "Não foi encontrado"
    GoTo StartOver

    End Sub

Private Sub cmdPequisar_Click()

For Each ws In ThisWorkbook.Worksheets(Array("2013", "2014", "2015"))
  'With ws.UsedRange

        With ws.Columns(1)
            Set c = .Find(txtCPF.Value, .Cells(.Cells.Count), xlValues, xlPart)

If Not c Is Nothing Then

txtCPF.Value = c.Value
txtNome.Value = c.Offset(0, 1).Value
txtEndereco.Value = c.Offset(0, 2).Value
cboEstado.Value = c.Offset(0, 3).Value
cboCidade.Value = c.Offset(0, 4).Value
txtTelefone.Value = c.Offset(0, 5).Value
txtEmail.Value = c.Offset(0, 6).Value
txtNascimento.Value = c.Offset(0, 7).Value


If c.Offset(0, 8) = "Masculino" Then
OptionButton1.Value = True
Else
OptionButton2.Value = True
End If
Else
MsgBox "Cliente não encontrado!"
End If
End With
Linha1:
Next
End Sub

Sub TenteAdptar()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    '
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "*************@gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*********"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With

    With Sheets("Sheet1")
    strbody = "Assunto:" & vbNewLine & vbNewLine & _
        .Range("A1")
    End With

    With iMsg
        Set .Configuration = iConf
        .To = "**********@gmail.com"
        .CC = ""
        .BCC = ""
        .From = """"" <*********@gmail.com>"
        .Subject = "Important message"
        .TextBody = strbody
        .Send
    End With


End Sub

Private Sub btnSubmit_Click()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim NewFile As String
Dim Flds As Variant
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    'Copia o atual Livro (xls) para outro com formatos e valores.
    'Está nova copia será anexada
    Call Create_File

    iConf.Load -1
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "our work server name"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Update
    End With
   
    With iMsg
        Set .Configuration = iConf
        .To = "Endereço Para:"
        .CC = ""
        .BCC = ""
        .From = "Endereço De:"
        .Subject = "Assunto - " & Range("C5").Value
        .AddAttachment "C:\NomeArquivo.xls" '<- local e nome do arquivo
        '.TextBody = strbody
        .Send
    End With
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub