Syntax for arrays and loops in VBA

Hi,
I do not code in VBA very often and struggle with loops, so I am hoping that some gurus out there can help me.

I have a list in Excel and a form in my workbook. This form
1) enables a user to input a search string into a text box and click search
2) my VBA so far can take that input text and go to my list, search down all the text descriptions in each cell in column D
3) if it find the users text in a cell in column D, it copies that row out to a new workbook

so the result is the user gets a separate workbook with a list of rows where their search string is in the description text that was in column D of my list.

I want to expand my code to let the user input more than 1 search word and they can separate each word with a #.
I would like to understand how I can take the user input, use the # delimiter to separate each search string and when my code goes to each description in Column D is searches for each of these search strings rather than just one search string.

This is the code so far,

Private Sub BTN_SEARCH_Click()
On Error GoTo Err_Execute

Application.ScreenUpdating = False

Dim thisWB  As String
    thisWB = ActiveWorkbook.Name

'check that a search string has been entered
If Trim(Me.TBX_WORD_SEARCH.Value) = "" Then
  Me.TBX_WORD_SEARCH.SetFocus
  MsgBox "Please enter a word search"
  Exit Sub
End If

' Put search text into a string variable
Dim str As String
    str = UCase(Me.TBX_WORD_SEARCH.Value)

 ' create new workbook for search results
Dim newWB As String
    Workbooks.Add
    newWB = ActiveWorkbook.Name

' Back to the CAATs workbook
Workbooks(thisWB).Activate
Sheets("CAATS List").Select

' Select 1st row headings in CAATs List to copy and copy to new workbook
Rows("1:1").Select
    Selection.Copy
Windows(newWB).Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

' Back to the CAATs workbook
Workbooks(thisWB).Activate
Sheets("CAATS List").Select

   
   'Start search in row 2
   LSearchRow = 2
   
   'Start copying data to row 2 in new workbook (row counter variable)
   LCopyToRow = 2
   
   While Len(Range("A" & CStr(LSearchRow)).Value) > 0
   
      'If value in column D contains the search string, copy entire row to new workbook
      If InStr(UCase(Range("D" & CStr(LSearchRow)).Value), str) > 0 Then
      
         'Select row in Sheet1 to copy
         Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
         Selection.Copy
         
         'Paste row into Sheet2 in next row
         Windows(newWB).Activate
         Sheets("Sheet1").Select
         Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
         ActiveSheet.Paste
         
         'Move counter to next row
         LCopyToRow = LCopyToRow + 1
         
         'Go back to CAATs List to continue searching
          Workbooks(thisWB).Activate
          Sheets("CAATS List").Select
         
      End If
      
      LSearchRow = LSearchRow + 1
      
   Wend
   
'Go to new workbook
    Windows(newWB).Activate
    Range("A1").Select
    
    Unload Me
    
   Exit Sub


Err_Execute:
   MsgBox "An error occurred."

End Sub

Looking forward to your creativity.