Solved: Match Name find Row No then copy values to sheet2

[Rob342]

Hi

I need to copy a user input on sheet 1 on rows C3,C5,C7,C9,C11 then find the name on C3 match the name on sheet 2 then copy the values from C5 to C11 on sheet 1 to columns B, D, F,G on sheet 2 against that name

C3 = NAME
C5 = GP
C7 = UNIT
C9 = DEPT
C11 = MARKUP

I have a routine for the name but can't find how to get the row so it can be copied into sheet 2.

Any help would be most appreciated.

[BrianMH]

can you give us an example of your current routine? Maybe upload an example workbook?

[Rob342]

Hi Brian

Thanks for the reply, example attached, have had a brain storm today the routine has eluded me.

Thanks
Rob

[BrianMH]

[vba]Sub InputData()
Dim strName As String
Dim strGP As String
Dim strUnit As String
Dim strDept As String
Dim strMarkup As String
Dim rngFind As Range

With ThisWorkbook.Worksheets("Sheet1")
strName = .Range("C3").Value
strGP = .Range("C5").Value
strUnit = .Range("C7").Value
strDept = .Range("C9").Value
strMarkup = .Range("C11").Value
End With

Set rngFind = Worksheets("Sheet2").Range("A:A").Find(strName, LookIn:=xlValues)
If Not rngFind Is Nothing Then
rngFind.Offset(0, 1).Value = strGP
rngFind.Offset(0, 3).Value = strUnit
rngFind.Offset(0, 5).Value = strDept
rngFind.Offset(0, 7).Value = strMarkup
Else: MsgBox ("Name not found")
End If

End Sub

[/vba]
That does the job for you. Personally I would add data validation to the name field on sheet 1 so it had a drop down of names.

[Rob342]

Thanks Brian

Works perfect, i owe you a beer.

Much Appreciated
Rob