Code to search and get client information

Question

I have an improvised customer registration spreadsheet inside Excel until I can develop one in the database like MySQL or Access, but after some changes I broke my code and I can't solve it.

It is a code to search for the client information by their full name and get their info if they are registered, but my spreadsheet only works on the base sheet of the service order, not on the customer's orders.

It is a work order spreadsheet, where I have the base OS sheet to make the new ones, and then I make a copy (by vba macro) for each customer of the week. But the search is only working and taking the information from the base OS, and putting it there, not the specific client OS.

I need it to work on all the sheets, including the one I'm trying to get the information from, and put this information on the right sheet. I tried to change the workbook calls from

ThisWorkbook.Sheets("OS").Range("B10").Value

to

ActiveSheet.Range("B10").Value

but it didn't work.

The code:

Sub search()
' search of data and information of registered clients
    Dim results As String
    results = MsgBox("Deseja buscar este cliente?", vbYesNo) 'message to confirm search
    
    If results = vbYes Then
        Application.ScreenUpdating = False
        
        client = ThisWorkbook.Sheets("OS").Range("B10").Value
        
        ThisWorkbook.Sheets("db").Activate
        Dim lastLine As Long
        lastLine = ActiveSheet.cells(rows.count, 1).End(xlUp).row
        
        For i = 2 To lastLine
            If cells(i, 1).Value = cliente Then
                client = cells(i, 1).Value
                cAddress = cells(i, 2).Value
                cNumber = cells(i, 3).Value
                district = cells(i, 4).Value
                City = cells(i, 5).Value
                Contact = cells(i, 6).Value
                cpf = cells(i, 7).Value
                car = cells(i, 8).Value
                lPlate = cells(i, 9).Value
                renavam = cells(i, 10).Value
                km = cells(i, 11).Value
                
                ThisWorkbook.Sheets("OS").Range("B10").Value = client
                ThisWorkbook.Sheets("OS").Range("B11").Value = cAddress
                ThisWorkbook.Sheets("OS").Range("F11").Value = cNumber
                ThisWorkbook.Sheets("OS").Range("B12").Value = district
                ThisWorkbook.Sheets("OS").Range("F12").Value = City
                ThisWorkbook.Sheets("OS").Range("F13").Value = Contact
                ThisWorkbook.Sheets("OS").Range("B13").Value = cpf
                ThisWorkbook.Sheets("OS").Range("B15").Value = car
                ThisWorkbook.Sheets("OS").Range("B16").Value = lPlate
                ThisWorkbook.Sheets("OS").Range("F15").Value = renavam
                ThisWorkbook.Sheets("OS").Range("F16").Value = km
            
                i = i + lastLine
            
            Else
                
                If i = lastLine Then
                    MsgBox "Cliente não cadastrado!" 'message for clients not registered
                    ThisWorkbook.Sheets("OS").Activate
                    Application.ScreenUpdating = True
                    Exit Sub
        
                Else
                End If
                
            End If
        
        Next i
    
        ThisWorkbook.Sheets("OS").Activate
        Application.ScreenUpdating = True
        MsgBox "Busca efetuada com sucesso!" 'successful search and get informations message
    
    End If

End Sub

PS. The spreadsheet is in Portuguese

Answer 1

Please, try the next code. It iterates between all existing sheets (except "db" one) and do what the existing code does for the client found in "B10:

Sub searchOneSheet()
' search of data and information of registered clients
    Dim results As VbMsgBoxResult, sh As Worksheet, wsDB As Worksheet
    Dim client As String, lastLine As Long, boolFound As Boolean, i As Long
  
    results = MsgBox("Deseja buscar este cliente (""B10"" Range)?", vbYesNo) 'message to confirm search
    
    If results <> vbYes Then Exit Sub
    
    Set wsDB = ThisWorkbook.Sheets("db")
    Set sh = ThisWorkbook.ActiveSheet
    
    lastLine = wsDB.cells(rows.count, 1).End(xlUp).row
    'Application.ScreenUpdating = False
            client = sh.range("B10").Value
            For i = 2 To lastLine
                If wsDB.cells(i, 1).Value = client Then
                    sh.range("B11").Value = wsDB.cells(i, 2).Value  'cAddress
                    sh.range("F11").Value = wsDB.cells(i, 3).Value  'cNumber
                    sh.range("B12").Value = wsDB.cells(i, 4).Value  'district
                    sh.range("F12").Value = wsDB.cells(i, 5).Value  'City
                    sh.range("F13").Value = wsDB.cells(i, 6).Value  'Contact
                    sh.range("B13").Value = wsDB.cells(i, 7).Value  'cpf
                    sh.range("B15").Value = wsDB.cells(i, 8).Value  'car
                    sh.range("B16").Value = wsDB.cells(i, 9).Value  'lPlate
                    sh.range("F15").Value = wsDB.cells(i, 10).Value 'renavam
                    sh.range("F16").Value = wsDB.cells(i, 11).Value 'km
                
                    boolFound = True: Exit For  'since you said there are not double occurrences
               End If
            Next i
            If Not boolFound Then
                MsgBox "Cliente não cadastrado (" & client & " - " & sh.Name & ")!" 'message for clients not registered
            Else
                MsgBox "Busca efetuada com sucesso (" & client & " - " & sh.Name & ")!" 'successful search and get informations message
                boolFound = False
            End If
    'Application.ScreenUpdating = True
End Sub