Hello there,
Below is a macro that provides an inputbox for you to enter the value you wish to search for in column A and then finds all instances of that value and copies its entire row and pastes it into a worksheet entitled "Sheet2".
To use this code press Alt and F8 at the same time on your keyboard. Then enter the text InputValue in the macro name box. Clear the contents if there is anything in there and then enter the text InputValue.
The select create. In between the Sub InputValue and End Sub paste the below code. Anything that appears in green is a comment I left to help you understand what the code was doing.
Right now the macro assumes your worksheet names are Sheet1 (copy from worksheet) and Sheet2 (copy to worksheet). Look through the code below and change the names of the worksheets to the names of your worksheets before running to ensure it works correctly.
'declare variables
Dim TextOutput As String, LR As String
Dim lCountText As Long
Dim FCText As Range
'provide and input box for the user to enter the search criteria
'set the variable TextOutput equal to the value you enter in the input box
TextOutput = InputBox(Prompt:="Type the value whose row you wish to copy.", _
Title:="Enter Value")
'on error exit sub
On Error GoTo errorhandler
'if the value you entered is nothing then msgbox stating so and exit the macro
If TextOutput = vbNullString Then
MsgBox "You did not enter a value"
Exit Sub
'else
Else
'set that variable FCText equal to the Range("A1") in the current worksheet
Set FCText = Range("A1")
'if there is no instance of the value you entered then tell the user
'the value was not found and exit sub
If WorksheetFunction.CountIf(Columns(1), TextOutput) = 0 Then
MsgBox "Text Not Found"
Exit Sub
'else
Else
''set the variable lCountText equal to the first instance of the numerical value
'you inputed to the last instance
For lCountText = 1 To WorksheetFunction.CountIf(Columns(1), TextOutput)
'set the variable FCText again to the found cell in Column A aka as 1
'whose value is equal to OutputText (the value you entered)
Set FCText = Columns(1).Find(What:=TextOutput, After:=FCText, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'with FCText (the found cell)copy the entire row and paste it into the first empty cell in
'column A of the worksheet Sheet2, you can change the Sheet2 to the
'name of the worksheet you wish to copy it to
With FCText
.EntireRow.Select
Selection.Copy
Sheets("Sheet2").Select
LR = Sheets("Sheet2").Range("A6555").End(xlUp).Row + 1
Range("A" & LR).Select
Selection.PasteSpecial
'reselect Sheet1 you'll need to change this to
'the name of the worksheet you are copying from
Sheets("Sheet1").Select
Application.CutCopyMode = False
End With
'move to the next instance
Next lCountText
End If
End If
errorhandler:
Exit Sub
To run the code press Alt F8 again and then select the InputValue Macro and select Run.
Let me know if this works for you!
Thanks!
Bookmarks