Retain a data value and remove other cell contents.

[Astronaut01]

Hello excel vba experts
Im quite new to excel vba and need to create a vba tool that can:
1. Search for all data value/s that has the "RTN000..." in column F. Some cells in column F has mulitple "RTNs..." and symbols and words.
2. Once found, it should copy that specific "RTN" together with all the row data, to a sheet i named Output.

Been trying to see if a use of wildcard would work but not sure how. Many thanks in advance for all your solutions.

 

[BSALV]

Private Sub CommandButton1_Click()
     Dim MyRTN, Arr6

     Set Sheet1sh = ThisWorkbook.Sheets("Sheet1")
     Set findapsh = ThisWorkbook.Sheets("findap")
     Set outputsh = ThisWorkbook.Sheets("output")

     outputsh.UsedRange.Clear                                   'clear output-sheet

     With findapsh
          MyRTN = Application.Transpose(.Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value)     'array with the wanted RTN's
     End With

     With Sheet1sh
          .AutoFilterMode = False                               'disable autofilter
          Set c = .Range("A1").CurrentRegion                    'range on with autofilter 'll be applied

     '*****1st option *******EXACT **********************************************
     'copy those rows, with an exact match !!!!
          c.AutoFilter 6, MyRTN, Operator:=xlFilterValues       'filter on that array
          c.Copy outputsh.Range("O1")                           'copy to output-sheet
     '***********************************************************************

     '*****2nd option ******CONTAIN **********************************************
     'you asked, cells that contain those RTN's, so extra check to collect all those in an array !!!!!
          Arr6 = Application.Transpose(c.Offset(1, 5).Resize(c.Rows.Count - 1, 1).Value)     'array of the databodyrange, 6th column
          For i = 1 To UBound(MyRTN)                            'loop through all wanted RTN's
               fl = Filter(Arr6, MyRTN(i), 1, vbTextCompare)    'all entries of the array that contain that RTN
               If UBound(fl) <> -1 Then s = s & "|" & Join(fl, "|")     'join them, with "|" as separator in a string
          Next
          vs = Split(Mid(s, 2), "|")                            'split the gathered entries on the "|"
          c.AutoFilter 6, vs, Operator:=xlFilterValues          'filter on that array
          c.Copy outputsh.Range("A1")                           'copy to output-sheet
     '***********************************************************************
          c.AutoFilter
     End With

     MsgBox ("Search result copied to output sheet.")
End Sub