Search Range for value , copy cell and paste into worksheet 2 same location

[HEALTHSTAFF]

Hello,
The title says it all I have a staff roster with many names. I have set up a drop down list on the side of the roster to select staff name.
I have also set up a completely blank second roster on worksheet 2. I need code to look through range of cells in worksheet 1, if a cell comes up with the value then copy cell to worksheet 2 in same location.

I am pretty sure i have the code to search that works.

Sorry on a work computer that is unable to have software installed to give a screenshot.

Many Thanks in Advance for your assistance
Craig

 

[My Aswer Is This]

Ok:
You said:
The title says it all

Search Range for value

My questions:
1. Search what range?
2.Search for what value.

You said:
I have set up a drop down list on the side of the roster to select staff name.

Do you mean search Column "A" for some value.

If you could provide more details maybe I could help.

And what will activate this script?
A click of a button or a selection from a datavalidation list

 

[HEALTHSTAFF]

Ok:
You said:
The title says it all

Search Range for value

My questions:
1. Search what range?
2.Search for what value.

You said:
I have set up a drop down list on the side of the roster to select staff name.

Do you mean search Column "A" for some value.

If you could provide more details maybe I could help.

And what will activate this script?
A click of a button or a selection from a datavalidation list

Sorry for being unclear.

It is a data validation list of names , once a name is selected press a button. This will be the search criteria located on cell Q4 , The search encompasses C3:P87
Hope this helps

 

[My Aswer Is This]

So this script will:

Search Sheet(1) Range("C3:P87")
For the value in Sheet(1) Range("Q4")

And if found this same value will entered into the same cell in Sheet(2)

So if "David" is in Sheet(1).Range("Q4")

We search the range in Sheet(1).Range(C3:P87") For "David"

If we find "David" in Range("C40") for example the value "David" will be put into Range("C40") in Sheet(2)

Sub Test()
Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim anss As Variant
ans = Sheets(1).Range("Q4").Value
For Each c In Sheets(1).Range("C3:P87")
    If c.Value = ans Then
        anss = c.Address
        Sheets(2).Range(anss).Value = ans
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Another option
This needs to go in the sheet module for the existing staff roster

Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim Fnd As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target = Range("Q4") Then Exit Sub
   Set Fnd = Range("C3:P87").Find(Target.Value, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      MsgBox "Not Found"
      Exit Sub
   End If
   Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range(Fnd.Address).Value = Fnd.Value

End Sub

This will run automatically whenever the value in Q4 is changed.
Change the sheet name in red to suit.

 

[HEALTHSTAFF]

Thanks for the code, unfortunately on sheet 2 it only finds and pastes 1 item. if on sheet 1 there is multiple of the name then it doesn't come across. Any Ideas?
Thanks Again

 

[My Aswer Is This]

That is true if you use Fluff script. So I guess you did not try my script because it will find multiples. Did you try my script?

Thanks for the code, unfortunately on sheet 2 it only finds and pastes 1 item. if on sheet 1 there is multiple of the name then it doesn't come across. Any Ideas?
Thanks Again

 

[HEALTHSTAFF]

Hello,
I decided to go with the first option and assign it to a button...works perfectly
Thanks
Heaps

 

[My Aswer Is This]

If you want a script to activate when you change the value in "Q4"
Try this:
This will work with multiples

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 1-3-2018 8:11 PM EST
If Target.Column = 17 And Target.Row = 4 Then
Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim anss As Variant
ans = Target.Value
    For Each c In Sheets(1).Range("C3:P87")
        If c.Value = ans Then
            anss = c.Address
            Sheets(2).Range(anss).Value = ans
        End If
    Next
End If
Application.ScreenUpdating = True
End Sub