Search using wildcard

Haven't tested, but did you try

Criteria1:=TextBox1.Value & "*"

Private Sub CommandButton1_Click()

    Dim rngFilter As Range
    
    With Sheets("S04FLXLS")
            .AutoFilterMode = False
        Set rngFilter = .Range("a1:a" & .Rows.Count).End(xlUp)
    End With
    
    With rngFilter
        .AutoFilter Field:=1, Criteria1:=TextBox1.Value & "*"
        .EntireRow.SpecialCells(12).Copy Destination:=Sheet2.Range("A1")
        .AutoFilter
    End With

End Sub

I modified it a bit, and what is happening is that it is taking the header row A1:N1 and transferring the header to Sheet 1. I cannot attach the file because it is not "For Public Display"

I should add that this search will likely generate multiple results. This search is designed to help people find a street when they only have a part of the name, so if the user enters "wood" into the Textbox, I would like all of the results from Column A containing "wood" to appear on sheet 2.

You were really, really close.

In addition to Paul's suggestion, change this;

        .EntireRow.SpecialCells(12).Copy Destination:=Sheet2.Range("A1")

to this;

        .Range("A1:N" & .Rows.Count).End(xlUp).SpecialCells(12).Copy Destination:=Sheet2.Range("A1")

Private Sub CommandButton1_Click()

    Dim rngFilter As Range
    
    With Sheets("S04FLXLS")
            .AutoFilterMode = False
        Set rngFilter = .Range("a1:b" & .Rows.Count).End(xlUp)
    End With
    
    With rngFilter
        .AutoFilter Field:=1, Criteria1:=TextBox1.Value & "*"
        .Range("A1:N" & .Rows.Count).End(xlUp).SpecialCells(12).Copy Destination:=Sheet2.Range("A1")
        .AutoFilter
    End With

End Sub

I tried that last suggestion, and now I'm back to the entire header row being printed on Sheet 1 and nothing going onto Sheet 2.

Take note of the changes below;

Private Sub CommandButton1_Click()

    Dim rngFilter As Range
    
    With Sheets("S04FLXLS")
            .AutoFilterMode = False
        Set rngFilter = .Range("B1:B" & .Rows.Count).End(xlUp)
    End With
    
    With rngFilter
        .AutoFilter Field:=2, Criteria1:=TextBox1.Value & "*"
        .Range("A1:N" & .Rows.Count).End(xlUp).SpecialCells(12).Copy Destination:=Sheet2.Range("A1")
        .AutoFilter
    End With

End Sub

Some info;

Your modified code had this;

        Set rngFilter = .Range("a1:b" & .Rows.Count).End(xlUp)

Your data is in colomn B so the search has to be in column B. Not columns A1 through the last row of column B (as your code was written). That was a typo/oversight on your part.
So, this is the correction that I have in the code I provided;

        Set rngFilter = .Range("B1:B" & .Rows.Count).End(xlUp)

Secondly, since you are now filtering Column B, The AutoFilter Field needed to be changed from Field:=1 to Field:=2

Change this;

      .Range("A1:N" & .Rows.Count).End(xlUp).SpecialCells(12).Copy Destination:=Sheet2.Range("A1")

to this;

        .Range("A1:E" & .Rows.Count).End(xlUp).SpecialCells(12).Copy Destination:=Sheets("Sheet2").Range("A1")

Originally Posted by tapsmiled

Sorry...one last question. I placed a command button on Sheet 3, where my data appears. What could I put to clear all of the cells after the search has been completed?

That would be;

    Cells.ClearContents

I wasn't too keen on the code you had, and saw a few small improvements that I thought were needed. So, here's the complete version of the code I did for you.

Private Sub CommandButton1_Click()
    Dim rngFilter As Range
    Dim LR As Long
    Application.ScreenUpdating = False
    With Sheet1
        .AutoFilterMode = False
        Set rngFilter = Range("B1:B" & Rows.Count).End(xlUp)
    End With
    
    With rngFilter
        .AutoFilter Field:=2, Criteria1:="*" & TextBox1.Value & "*"
          LR = Range("B" & Rows.Count).End(xlUp).Row
         .Range("A1:E" & LR).SpecialCells(12).Copy Destination:=Sheets("Sheet2").Range("A1")
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

You're welcome. Please mark this thread as Solved, and please leave a Rep for me! Thanks.

To prevent the headers from being cleared, you will need this;

        Dim LR As Long
          LR = Range("A" & Rows.Count).End(xlUp).Row
                  Cells.Range("A2:Z" & LR).ClearContents

Whereas, Z is the last column that I guessed where your data extends to. You can change the value of Z to whatever suits the last column of your data.




The line;

    Application.ScreenUpdating = False

will prevent the screen from flickering when as the code runs.
After the code completes, the last line;

    Application.ScreenUpdating = True

allows the screen to refresh.

With code, there is always a way to do something.

I ask you to start a new topic on your latest question. That way, if someone else has the same question/needs the same answer, they can look it up online, by topic.

Thanks!