find value in a column and copy the row

[sarahips]

I have seen other posts asking a similar question but I dont know VBA well enough to be able to fit it to my needs so help would be appreciated:

I have a spreadsheet that contains staff information. I want to search for a staff member (column B) by entering the staff number in an input box and then copying the entire row (A:Z) onto a different spreadsheet, in a separate workbook if possible. There may be more than one entry per staff number and I need to copy all occurances.

I also want run the macro from another 'control' spreadsheet.

Eg, Sheet1 is the control sheet, Sheet2 contains the information, Sheet3 is where the selected information is copied to.

First time posting so please let me know if I need to post more information

Thanks

 

[John Davis]

I have seen other posts asking a similar question but I dont know VBA well enough to be able to fit it to my needs so help would be appreciated:

I have a spreadsheet that contains staff information. I want to search for a staff member (column B) by entering the staff number in an input box and then copying the entire row (A:Z) onto a different spreadsheet, in a separate workbook if possible. There may be more than one entry per staff number and I need to copy all occurances.

I also want run the macro from another 'control' spreadsheet.

Eg, Sheet1 is the control sheet, Sheet2 contains the information, Sheet3 is where the selected information is copied to.

First time posting so please let me know if I need to post more information

Thanks

Not sure what you mean by control sheet, but maybe this will help?

Sub sarahips()
Dim x As String
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

x = InputBox("Please enter the Staff Member's Name")

With Range("B2:B" & lr)

    .AutoFilter Field:=1, Criteria1:=x
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
    .AutoFilter
    .AutoFilter
    
End With

End Sub

 

[sarahips]

Hi, thanks for this. It's not quite doing everything I need but it's certainly given me a good starting point.
thanks for your quick response

 

[sarahips]

Is it possible to copy the values of the cells only?
This one is copying all the fornulas too, including a macro button I have at the end of the row!

Thanks

 

[John Davis]

Is it possible to copy the values of the cells only?
This one is copying all the fornulas too, including a macro button I have at the end of the row!

Thanks

You're welcome. See if this helps.

Sub sarahips()
Dim x As String
Dim i As Long
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

x = InputBox("Please enter the Staff Member's Name")

For i = lr To 2 Step -1

    If Range("B" & i).Value = x Then
    
        Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).Value = Range(Range("A" & i), Range("Z" & i)).Value

    End If
    
Next i

End Sub

 

[sarahips]

I'll try that, thanks