Macro to Move row append to new worksheet with user input messagebox

Greetings everyone,

Had a long winded post, however clicked the wrong button and lost it all.
The attached Template.xls is one of several hundred we have here to plow through.
(Names and places have been changed to protect the innocent.)

1. Key_Word_Search_Yes_No_Cancel
2. KeyWordSearch
3. Search_Move_Row

Three macros are included. 1st one I broke... I admit this. So many attempts to make this thing work.
2nd and 3rd Macro's are based off code from Tom Urtis with modifications of course. (excellent code btw)
These are nice due to end report showing how many where located and displayed to the user.

These find the correct entries but overwrite each other instead of appending.
(coding some of the common searches would be nice rather than plucking at the keyboard over and over for the same search criteria.)

Ideally since each workbook can have 3 or 20+ worksheets. It would speed up the process if the macro Searched the entire workbook.
Appending to "Contents" worksheet found in each excel file under "Drawing Binder". Deleting the original row source and leaving no blank rows.
Be happy to just shove it all under sheet2 if this simplifies the process.

Under the template.xls sheet2 shows yellow highlighted items that are picked up but not needed. (not sure how to exclude this from the search)
The common search criteria inputs are:
ROLL - 8"
ROLL-8"
MM FACE
8" ROLL
12" ROLL
304.8MM

Note: there are others but not as common. Hence the need to search these automatically then loop to ask for any other inputs from the user.
Macro's 2 and 3 work but only for one search, one worksheet at a time. This would take a long time to complete.
Looking forward to any help and guidance.

And yes.. final output sorted by column C
Column "B" is hidden but needs to go along for the ride over to "Contents" or "Sheet2" whichever helps simplify the process.

More description or details needed perhaps?
Sorting can be done manually after all the searches are completed.
These Excel workbooks go back a few years, however all have the same basic layout.
Will be working with these macros this week and next. Any day now the tap on the shoulder will come to start this tedious task.

Let me try a different tact...
Basically this macro works but so far not able to get this to append when activating this line:
'Set wsDest = ActiveWorkbook.Worksheets("Sheet2")
While deactivating this line:
Set wsDest = ActiveWorkbook.Worksheets.Add
Currently the macro adds a new worksheet each time the macro is run and valid search criteria is input.
It also will only search within the active worksheet. To avoid running through "Many" spreadsheets per workbook.
Would be great if it search the entire workbook. doable?

Sub Key_Word_Search()
Dim Ret_type As Integer
Dim strMsg As String
Dim strTitle As String
Dim myValue As Variant

' Dialog Message

strMsg = "Search current Worksheet?"
' Dialog's Title
strTitle = "Search WorkSheet"
'Display MessageBox
    Ret_type = MsgBox(strMsg, vbYesNoCancel + vbQuestion, strTitle)
' Check pressed button
Select Case Ret_type

Case 6
myValue = strTest
strTest = InputBox("Your Search Criteria?")
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim i As Long
Dim rngCells As Range
Dim rngFind As Range
    
    Set wsSource = ActiveSheet
    
    NoRows = wsSource.Range("A65536").End(xlUp).Row
    DestNoRows = 2
    Set wsDest = ActiveWorkbook.Worksheets.Add
    'Set wsDest = ActiveWorkbook.Worksheets("Sheet2")
    For i = 1 To NoRows
    
        Set rngCells = wsSource.Range("D" & i & ":F" & i)
        
        If Not (rngCells.Find(strTest) Is Nothing) Then
            rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)
            DestNoRows = DestNoRows + 1
     
        End If
    Next i

Case 7
    MsgBox "Click ok Search again"
    Call Key_Word_Search
Case 2
    MsgBox "Quit?"
End Select
End Sub
Sub Choose()
Dim Ret_type As Integer
Dim strMsg As String
Dim strTitle As String
' Dialog Message
strMsg = "Click any one of the below buttons."
' Dialog's Title
strTitle = "Search for Morgan WorkSheets"
'Display MessageBox
    Ret_type = MsgBox(strMsg, vbYesNoCancel + vbQuestion, strTitle)
' Check pressed button
Select Case Ret_type
Case 6
    MsgBox "You clicked 'YES' button."
Case 7
    MsgBox "You clicked 'NO' button."
Case 2
    MsgBox "You clicked 'CANCEL' button."
End Select
End Sub