HELP, PLEASE?!

[timothtcraig1]

I'm using Microsoft 365, I have an Excel workbook, and a sheet within the workbook with every internet lead we have gotten this year (big). I select the "Find & Search" box and input text, then click "Find All", and it pops back 423 rows. I click one row, then I hit "Ctrl A" and it highlights all the rows. HERE'S WHERE THE PROBLEM BEGINS...

I need to copy and/or cut all the highlighted rows in the find results, then open a new sheet and paste them into it, and I cannot figure out how to do it?? I have read countless similar questions here, and all their answers, and tried every one of them, with zero success? I have no idea what I'm doing wrong?? I have to get this NEW sheet to my boss yesterday?? PLEASE HELP?! Thank you!

 

[jolivanes]

Another way.
Check and change references like sheet names as required.
Row 1 of the sheet with all the data (ws1) is a header row (normal good practice)

Sub Like_So()
Dim lr As Long, lc As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim sVal As String
sVal = Application.InputBox("Text to search for.", "Supply Search Name.", , , , , , 2)
Set ws1 = Worksheets("Sheet1")    '<---- Sheet with all data. Change name as required
Application.ScreenUpdating = False
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sVal
Set ws2 = Worksheets(sVal)
lr = ws1.Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = ws1.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    With ws1.Cells(2, lc + 1).Resize(lr - 1)
        .Formula = "=IF(COUNTIF(RC[-" & lc & "]:RC[-1], """ & sVal & """) = 0, ""No"", ""Yes"")"
        .Value = .Value
    End With
    With ws1
        .Range(.Cells(1, 1), .Cells(lr, lc + 1)).AutoFilter lc + 1, "Yes"
        .Range(.Cells(2, 1), .Cells(lr, 1)).Resize(, lc).Copy ws2.Cells(1, 1)
        .Cells(1, 1).AutoFilter
        .Columns(lc + 1).ClearContents
    End With
Application.ScreenUpdating = True
End Sub

Note. To avoid possible typing mistakes, when the input box is up, select and click on a cell that has the search value, Lake Murray in this case, in it.

 

[timothtcraig1]

This makes quite boring creating a conventional formula (without using a helper column); so I reverted my mind to a macro.
For example:

Sub FilteredList()
Dim dArr, I As Long, J As Long, oSh As Worksheet
Dim fBy As String, LastR As Long, NextR As Long

Sheets("Sheet1").Select             '<<< The sheet with the unfiltered datas
fBy = "Lake Murray"                 '<<< The string to search for
Set oSh = Sheets("Sheet2")          '<<< The sheet where create the filtered list
'
LastR = Range("A:K").Find(What:="*", After:=Range("A1"), _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
oSh.Range("A:K").ClearContents              'Clear the destination sheet
dArr = Range("A1:K1").Resize(LastR).Value   'Copy the source data
For I = 1 To UBound(dArr)                   'Search in any row..
    For J = 1 To UBound(dArr, 2)            '...and any column..
        If dArr(I, J) = fBy Or I = 1 Then   '... the Filter string
            NextR = NextR + 1
            oSh.Cells(NextR, 1).Resize(1, UBound(dArr, 2)).Value = _
               Application.WorksheetFunction.Index(dArr, I, 0)
            Exit For
        End If
    Next J
Next I
End Sub

The lines marked <<< need to be adapted to your situation, according the comments

Insert the macro into a "standard vba module" of your file, and update the parametres as I sayd above; then run Sub FilteredList. It will clear the output range, then scan the source data and copy only those that match the filter condition

Try...

Sorry for the delay, brother! It was unavoidable, had to run down to Florida for work. I meant no disrespect, sincerely.

Anthony, I have never done a formula before? For example, I don't even know how to start one? Where do I copy and paste all this text?

 

[Anthony47]

Well, that is not a formula but a Macro; you have to copy the code and insert it into the "vba project" of your excel file. To do that, starting from your Excel workbook:
-type Alt-F11 to open the vba editor
-use Menu /Insert /Module to insert a "standard module"
-copy the given code and paste in into the rigth frame of the module just created

Modify the lines marked <<< according the comments
Beware that the sheet declared in the line Set oSh = Sheets("Sheet2") (Sheet2, in this initial declaration) should already exist when running the macro, or a run-time error will arise.

When you are done, return to Excel and run the macro.
To run a macro, from Excel: press Alt-F8; select the name of the macro to be excecuted (FilteredList, in this case), press Run

This should create on the "output sheet" a table that contains only the filtered rows.

In case of error when running the macro:
-take note of the type of error
-press "Debug" to view which instruction created the error (it is highlighted)
-write these information in your next message


Try...