Copy a certain row based on the contents of another row

[UncleBajubjubs]

Good morning,
I have a sheet in which the user selects criteria for a motor, such as horsepower, amount of usage, size constraints, etc, and then it will give the component name of the motor which best matches those criteria, such as "Motor #324880932 ", etc. Another sheet lists all the motor models (each on its own row) and all of their attributes.
Currently, a user will enter their info and then find that model number in the massive table. I am trying to make a macro to look on the table and find the model number in column B, then copy that entire row to a designated row on the main sheet. I'm wondering what the easiest way to go about this is.
Thanks!

 

[My Aswer Is This]

For me to help you I would need more specific details.

Like where will you enter the search criteria and where should we search for this criterial.

And then on what sheet will we copy the row to.

We always need specific detail.

Like all sheet names

What columns to search for what criterial

Please indicate column by saying something like

Like search: Sheet("Data") column G

Do not say search the other sheet column Motors

 

[UncleBajubjubs]

For me to help you I would need more specific details.

Like where will you enter the search criteria and where should we search for this criterial.

And then on what sheet will we copy the row to.

We always need specific detail.

Like all sheet names

What columns to search for what criterial

Please indicate column by saying something like

Like search: Sheet("Data") column G

Do not say search the other sheet column Motors

The value which will be searched for is on page Sheet("Motor Selection") Cell "R1".

The value will be searched for in Sheet("Motor Designs") Column B.

The row which contains the value will be copied to Sheet("Motor Selection" Row 132.

Thank you.

 

[My Aswer Is This]

Try this:

Sub Search_Me()
'Modified 8/5/2019 12:24:44 PM  EDT
Application.ScreenUpdating = False
Dim r As Long
Dim lastrow As Long
lastrow = Sheets("Motor Designs").Cells(Rows.Count, "B").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
SearchString = Sheets("Motor Selection").Range("R1").Value
Set SearchRange = Sheets("Motor Designs").Range("B1:B" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
r = SearchRange.Row
Sheets("Motor Designs").Rows(r).Copy Sheets("Motor Selection").Rows(132)
Application.ScreenUpdating = True
End Sub

 

[UncleBajubjubs]

Try this:

Sub Search_Me()
'Modified 8/5/2019 12:24:44 PM  EDT
Application.ScreenUpdating = False
Dim r As Long
Dim lastrow As Long
lastrow = Sheets("Motor Designs").Cells(Rows.Count, "B").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
SearchString = Sheets("Motor Selection").Range("R1").Value
Set SearchRange = Sheets("Motor Designs").Range("B1:B" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
r = SearchRange.Row
Sheets("Motor Designs").Rows(r).Copy Sheets("Motor Selection").Rows(132)
Application.ScreenUpdating = True
End Sub

This seems to have worked perfectly, thank you!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

This seems to have worked perfectly, thank you!

 

[UncleBajubjubs]

Followup question, I have a second workbook which copies over certain cells from the row which gets copied in this workbook.

Sub Moveinfo()



    Dim filename As Variant
    Dim equals As String
    Dim sUserName As String
    
'Turn off Automatic Calculation
    Application.Calculation = xlCalculationManual
     


     filename = Application.GetOpenFilename()
     If filename = False Then Exit Sub
     
     
     Worksheets("Control Panel").Activate


 
     
     


     Range("LinkedWorkbook") = filename
' Get Current User Name
sUserName = Environ$("username")
Range("LinkUser") = sUserName & " on " & Date


'pull in values
 Range("LinkedData1") = "='" & filename & "'!DataExport1"
'And then it copies over all the cells I need and puts them where the info belongs, I cut out the rest of these lines.

Is there a way I might modify the code you gave so it will run when this macro is run in the other workbook? I want to make sure the user doesn't copy over the wrong row, which may occur if they neglect to run the macro in the first workbook. Thanks.

 

[My Aswer Is This]

I'm not sure I can help you with your second request. I never like working with other scripts users have that I do not undersatand. Working with scripts between two different workbooks is not something I'm good at. Maybe you should post a new question on the forumn asking for help to combine both scripts.

Followup question, I have a second workbook which copies over certain cells from the row which gets copied in this workbook.

Sub Moveinfo()



    Dim filename As Variant
    Dim equals As String
    Dim sUserName As String
    
'Turn off Automatic Calculation
    Application.Calculation = xlCalculationManual
     


     filename = Application.GetOpenFilename()
     If filename = False Then Exit Sub
     
     
     Worksheets("Control Panel").Activate


 
     
     


     Range("LinkedWorkbook") = filename
' Get Current User Name
sUserName = Environ$("username")
Range("LinkUser") = sUserName & " on " & Date


'pull in values
 Range("LinkedData1") = "='" & filename & "'!DataExport1"
'And then it copies over all the cells I need and puts them where the info belongs, I cut out the rest of these lines.

Is there a way I might modify the code you gave so it will run when this macro is run in the other workbook? I want to make sure the user doesn't copy over the wrong row, which may occur if they neglect to run the macro in the first workbook. Thanks.

 

[UncleBajubjubs]

I'm not sure I can help you with your second request. I never like working with other scripts users have that I do not undersatand. Working with scripts between two different workbooks is not something I'm good at. Maybe you should post a new question on the forumn asking for help to combine both scripts.

No worries, I found an easier solution, a message box which asks them to verify that they ran the macro in the other workbook. Thanks!

 

[My Aswer Is This]

Glad to hear back from you and you created a solution.

No worries, I found an easier solution, a message box which asks them to verify that they ran the macro in the other workbook. Thanks!