Sub Macros2()
Application.DisplayAlerts = False
On Error GoTo MsgErr
 Dim iLastRow As Long
 Dim j As Long
 Dim i As Long
 Dim LastRowReport As Long
 Dim ТекстДляПоиска As Long
 ТекстДляПоиска = LCase(Trim(Me.TextBox1.Text))
 iLastRow = Cells(Rows.Count, 2).End(xlUp).Row
 Range(Cells(3, 1), Cells(iLastRow + 1, 24)).ClearContents
 iLastRow = 2
    For j = 1 To Sheets.Count - 1
        With Sheets(j)
            LastRowReport = .Cells(Rows.Count, 2).End(xlUp).Row
            For i = 1 To LastRowReport
                If LCase(Trim(.Cells(i, 2).Value)) = ТекстДляПоиска Then
                    Range(Cells(iLastRow + 1, 1), Cells(iLastRow + 1, 23)).Value = Range(.Cells(i, 1), .Cells(i, 23)).Value
                    Cells(iLastRow + 1, 24) = Sheets(j).Name
                    iLastRow = iLastRow + 1
            End If
            Next
        End With
    Next
Application.DisplayAlerts = True
MsgBox "Данные выгружены! Если данных нет, проверте корректность введенных данных", vbExclamation + vbOKOnly
Exit Sub
MsgBox "Данные выгружены!", vbExclamation + vbOKOnly
MsgErr:
    MsgBox "Что-то пошло не так! Проверте данные", vbExclamation + vbOKOnly
    Application.DisplayAlerts = True
End Sub

Function MyGeocode(address As String) As String 
  Dim strAddress As String 
  Dim strQuery As String 
  Dim strLatitude As String 
  Dim strLongitude As String 
  strAddress = URLEncode(address) 
  'Assemble the query string 
  strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?" 
  strQuery = strQuery & "address=" & strAddress  
  strQuery = strQuery & "&sensor=false" 
  'define XML and HTTP components 
  Dim googleResult As New MSXML2.DOMDocument 
  Dim googleService As New MSXML2.XMLHTTP 
  Dim oNodes As MSXML2.IXMLDOMNodeList 
  Dim oNode As MSXML2.IXMLDOMNode 
  'create HTTP request to query URL - make sure to have 
  'that last "False" there for synchronous operation 
  googleService.Open "GET", strQuery, False 
  googleService.send 
  googleResult.LoadXML (googleService.responseText) 
  Set oNodes = googleResult.getElementsByTagName("geometry") 
  If oNodes.Length = 1 Then 
    For Each oNode In oNodes 
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text 
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text 
      MyGeocode = strLatitude & "," & strLongitude 
    Next oNode 
  Else 
    MyGeocode = "Not Found (try again, you may have done too many too fast)" 
  End If 
End Function
 
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String 
  Dim StringLen As Long: StringLen = Len(StringVal) 
  If StringLen>0 Then 
    ReDim result(StringLen) As String 
    Dim i As Long, CharCode As Integer 
    Dim Char As String, Space As String 
    If SpaceAsPlus Then Space = "+" Else Space = "%20" 
    For i = 1 To StringLen 
      Char = Mid$(StringVal, i, 1)   
      CharCode = Asc(Char) 
      Select Case CharCode 
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126 
        result(i) = Char 
      Case 32 
        result(i) = Space 
      Case 0 To 15 
        result(i) = "%0" & Hex(CharCode) 
      Case Else 
        result(i) = "%" & Hex(CharCode) 
      End Select 
    Next i 
    URLEncode = Join(result, "") 
  End If 
End Function 

Sub Кнопка3_Щелчок()
Application.ScreenUpdating = False
With Sheets("Данные")
    .Visible = xlSheetVisible
Worksheets("Данные").Select
Lr = 2
While Cells(Lr, 1) <> ""
Lr = Lr + 1
Wend
    Dim sourceRange As Range
    Dim destrange As Range
    'Lr = LastRow_1(Sheets(2)) + 1
    Set sourceRange = Worksheets("Данные").Range("a1:u" & Lr)
    Set destrange = Worksheets("Для Алексея").Range("a2")
    sourceRange.Copy destrange
    Worksheets("Для Алексея").Select
    .Visible = xlSheetVeryHidden
End With
Application.ScreenUpdating = True
End Sub