Sub that runs through worksheets and reports an input value on another worksheet

[Dstars21]

I'm currently trying to create a sub that does the following:

1 - asks for an input (a string/name in this case)

2- loops through worksheets to see if their name is there, and if it is, reports their name and the "amount sold" in the column next to their name onto another worksheet.

what i have so far (i get an error with the current range i have set):

[FONT=verdana]Sub question3()


Dim ws As Worksheet
Dim emprng As Range
Dim I As Integer
Dim namesearch As String


    For Each ws In Worksheets
        Set emprng = ws.Range(Range("A2"), Range("A2").End(xlDown))
            If ws.CodeName <> "Sheet1" Then
                    namesearch = InputBox("Please enter the name of the employee you want to find.")
                        For I = 1 To emprng.Rows.Count
                            If emprng.Cells(I, 1) = namesearch Then
                                ' Report the name found onto another worksheet
                                Else
                                MsgBox ("cannot find that person in the data.")
                            End If
                        Next I
            End If

    Next ws

End Sub[/FONT]

 

[JLGWhiz]

this uses a different method to find the value than your code, but it should be a little faster since it eliminates the second For loop. Also in the comment you will see an example of how to put the found value on the destination worksheet.

Sub question3()
Dim ws As Worksheet
Dim emprng As Range
Dim I As Integer
Dim namesearch As String, fn As Range
    For Each ws In Worksheets
        If ws.CodeName <> "Sheet1" Then
            namesearch = InputBox("Please enter the name of the employee you want to find.")
                Set fn = ws.Range("A:A").Find(namesearch, , xlValues, xlWhole)
                    If Not fn Is Nothing Then
                        ' Report the name found onto another worksheet [COLOR=#008000]destSheet.cells(Rows.Count, 1).End(xlUp)(2) = fn.Value[/COLOR]
                    Else
                        MsgBox ("cannot find that person in the data.")
                    End If
            End If
    Next ws
End Sub

 

[Dstars21]

Thanks for the help! How would i get it to also report the 2 values in the columns next to the seached name?

 

[JLGWhiz]

Thanks for the help! How would i get it to also report the 2 values in the columns next to the seached name?

[COLOR=#008000]destSheet.cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = fn.Resize(, 3).Value[/COLOR]