copy data from sheet to another based on name by inputbox

hi nankw83 it should enter the name into inputbox as for this

Originally Posted by nankw83

Hi ABDELFATTA,

If found in Sheet2, it should update all cells of the same row ?

yes i would this but forget copy to the bottom if name is not existed should show message not data found

Try below code ...

Sub test()

Dim StrName$, Rg1 As Range, Rg2 As Range
StrName = InputBox("enter name")
Set Rg1 = Sheets("Sheet1").Columns(2).Find(StrName, lookat:=xlWhole)

If Len(StrName) = 0 Then
   MsgBox "Nothing was entered !", vbExclamation: Exit Sub
ElseIf Rg1 Is Nothing Then
   MsgBox StrName & " doesn't exist in Sheet1", vbExclamation: Exit Sub
End If

With Sheets("Sheet2")
   Set Rg2 = .Columns(2).Find(StrName, lookat:=xlWhole)
   If Not Rg2 Is Nothing Then
      Rg2.Resize(, 4) = Rg1.Resize(, 4).Value
   Else
      .Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 4) = Rg1.Resize(, 4).Value
      .Cells(Rows.Count, 1).End(xlUp).Offset(1) = .Cells(Rows.Count, 1).End(xlUp).Value + 1
   End If
End With
   
End Sub

In order for the search to be case sensitive, add the below to the above code red syntax

Sub test()

Dim StrName$, Rg1 As Range, Rg2 As Range
StrName = InputBox("enter name")
Set Rg1 = Sheets("Sheet1").Columns(2).Find(StrName, lookat:=xlWhole, MatchCase:=True)

If Len(StrName) = 0 Then
   MsgBox "Nothing was entered !", vbExclamation: Exit Sub
ElseIf Rg1 Is Nothing Then
   MsgBox StrName & " doesn't exist in Sheet1", vbExclamation: Exit Sub
End If

With Sheets("Sheet2")
   Set Rg2 = .Columns(2).Find(StrName, lookat:=xlWhole, MatchCase:=True)
   If Not Rg2 Is Nothing Then
      Rg2.Resize(, 4) = Rg1.Resize(, 4).Value
   Else
      .Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 4) = Rg1.Resize(, 4).Value
      .Cells(Rows.Count, 1).End(xlUp).Offset(1) = .Cells(Rows.Count, 1).End(xlUp).Value + 1
   End If
End With
   
End Sub

Glad to help & thanks for the added Rep