0

I have a excel sheet with around 50k rows and i need a macro to search for a cell in that sheet and if it finds it to copy the entire row to another sheet, my problem is that the keyword may be on multiple rows so if there are like 4 cells with that keyword i need it to copy all 4 rows and paste them in another sheet



Dim intPasteRow As Integer
intPasteRow = 2

Sheets("Sheet2").Select
Columns("A:AV").Select
On Error Resume Next
Selection.Find(What:="m12", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=True, SearchFormat:=True).Activate
If Err.Number = 91 Then
    MsgBox "ERROR: 'Keyword' could not be found."
    Sheets("Sheet1").Select
    End
End If

Dim intRow As Integer
intRow = ActiveCell.Row
Rows(intRow & ":" & intRow).Select
Selection.Copy

Sheets("Sheet1").Select
ActiveSheet.Paste

End Sub
Sub saci()

Dim rng As Range
Set rng = Range(ActiveCell, ActiveCell.Offset(10000, 0))
rng.EntireRow.Select
With Selection.EntireRow
.Cut
.Offset(.Rows.Count + 1).Insert
.Select
End With
Range("A4").Select

End Sub

so far its finding the first "m12" cell in Sheet2 and copies the entire row to Sheet1, how do i make it continue to search after finding "m12" and copy all rows with the "m12" in them instead of just the first one?

BigBen
  • 46,229
  • 7
  • 24
  • 40
Aceleon
  • 3
  • 2
  • 2
    Use `FindNext` https://learn.microsoft.com/en-us/office/vba/api/excel.range.findnext – SJR Nov 04 '19 at 12:49
  • 3
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also you must remove `On Error Resume Next`. This line just hides **all** error messages but the errors still occur. If you cannot see the messages you will not noice any errors so you cannot fix them. If you don't fix them your code cannot work properly. Remove that line and fix your errors or implement a full error handling: See ([VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling)). – Pᴇʜ Nov 04 '19 at 12:55

0 Answers0