-1

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

Sub saca()

Dim intPasteRow As Integer
intPasteRow = 2
Dim ceva As Range
Dim FirstAddress As String
Dim intRow As Integer

Sheets("Sheet2").Select
Columns("A:AV").Select
On Error Resume Next
Set ceva = Selection.Find(What:="m762", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=True, SearchFormat:=True).Activate
If Not ceva Is Nothing Then
    FirstAddress = ceva.Address
    Do
        Set ceva = Selection.FindNext(ceva).Activate
    Loop While Not ceva Is Nothing And ceva.Address <> FirstAddress
End If

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

Sheets("Sheet1").Select
ActiveSheet.Paste

End Sub

So far its searching for "m762" in Sheet2 but it only copies the first row with a "m762" cell instead of selecting all of them...I can't find a way to make it select all rows with "m762" in them

Tim Stack
  • 3,209
  • 3
  • 18
  • 39
Aceleon
  • 3
  • 2
  • with `intRow = ActiveCell.Row` you will get just one cell row, so on `Rows(intRow & ":" & intRow).Select` you are just selecting one row What are you trying to do with this code? (I think it should be `Rows(intRow & ":" & intColumn).Select` and `intColumn` should be defined as `intColumn = ActiveCell.Column`. Please, show some sample to get some help. – David García Bodego Nov 05 '19 at 08:22
  • i have a sheet with around 50k entries, each row being an entry and each one of them has a "user code" (the "m762" i'm searching for being one of them), i'm trying to search for a user ("m762") and copy all the rows he's present on in another sheet – Aceleon Nov 05 '19 at 08:29
  • 1
    You could look into AutoFilter – JvdV Nov 05 '19 at 08:37
  • @Aceleon... I already understand your question but without some sample, it is difficult to know. For 50k data it will be just easier add one more row that will have a blank when it is not matching with the user and not blank when it is matching, I will filter by blank values, so you just need to copy all the visible data. But it will be a generic answer to a generic question. – David García Bodego Nov 05 '19 at 08:37
  • Can "m762" be in one column, in a few columns, or in any column? – GMalc Nov 05 '19 at 13:32
  • Possible duplicate of [Excel macro to search for a keyword and and copy the entire row to another sheet](https://stackoverflow.com/questions/58693774/excel-macro-to-search-for-a-keyword-and-and-copy-the-entire-row-to-another-sheet) – Cindy Meister Nov 05 '19 at 13:43

1 Answers1

0

Although not the best or quickest solution, here is a basic For Loop macro that will loop through each cell and when the criteria is found, it will copy the row in Sheet2 to the next empty row in Sheet1 by setting the values directly from Sheet2 to Sheet1. It will then continue and copy each row where the criteria is found.

Dim cel As Range, lRow As Long

    For Each cel In ThisWorkbook.Sheets("Sheet2").Range("A2:AV" & Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row)
        If cel.Value = "m762" Then
            lRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

            ThisWorkbook.Sheets("Sheet1").Cells(lRow + 1, 1).Resize(, 48).Value = ThisWorkbook.Sheets("Sheet2").Cells(cel.Row, 1).Resize(, 48).Value
        End If
    Next cel
GMalc
  • 2,608
  • 1
  • 9
  • 16
  • this works but it only searches the first ~150 rows...can't seem to make it search all the way down – Aceleon Nov 06 '19 at 07:53
  • Updated, I did not change the second worksheet in the first line to `Sheet2`, so it is using `Sheet1` row count which probably has only 150 rows filled. – GMalc Nov 06 '19 at 13:48