excel...Copy row based on word in cell

[sandy armstrong]

Hello and goodMorning,

Can anyone please help me with this. I have a database in excel that has 2 sheets full of data. I would like if i press a button to update then macro searches in Column O if the words " Req. Feed Set Up" then it get move over to sheet 2 in the workbook.

Sub Feed_set_up()

Dim intLastRow As Long
intLastRow = ActiveSheet.UsedRange.Rows.Count

'Let's start at row 2. Row 1 has headers
X = 4

'Start the loop
Do While X <= intLastRow
'Look for data with 'Feed set up'
If Cells(X, 15) = "Req. Feed Set Up" Then
'copy the row if it contains '
Worksheets("Sheet1").Rows(X).Copy
'Go to sheet2. Activate it. We want the data here
Worksheets("Sheet2").Activate
'Find the first empty row in sheet2
erow = Sheet2.Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Row
'Paste the data here
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
End If
'go to sheet1 again and actvate it
Worksheets("Sheet1").Activate
'Loop through the other rows with data
X = X + 1

Loop
End Sub

I have this code(above)which works great but Everytime i press the update button it copy and paste the rows with the words in Column O overwrites what is in sheet 2. In other words i doesnt not find the fist blank row to paste the data it always starts at row 2 can i get some help with changing this around....
Thanks

[Guido Geurs]

Sandy,
You want to filter the rows with the text "Req. Feed Set Up" in col "O" (15) and transfer these records (rows) to sheet 2 ?

If so, this is the code:

Sub Feed_set_up()
Dim LASTROW As Long
Dim ROWidx As Integer
    LASTROW = Range("A2").End(xlDown).Row
    '§ Start the loop
    For ROWidx = 2 To LASTROW
        '§ Look for data with "Req. Feed Set Up"
        Range("O" & ROWidx).Activate
        If Range("O" & ROWidx).Value = "Req. Feed Set Up" Then
            '§ copy the row if it contains
            Range("A" & ROWidx).Resize(, Range("A" & ROWidx).End(xlToRight).Column).Copy
            Worksheets("Sheet2").Activate
            If Range("A1") = "" Then
                Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                        False, Transpose:=False
            ElseIf Range("A2") = "" Then
                Range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                        False, Transpose:=False
            Else
                Range("A" & Range("A1").End(xlDown).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                        False, Transpose:=False
            End If
        End If
        '§ go to sheet1 again and actvate it
        Worksheets("Sheet1").Activate
    Next
End Sub

PS: if it's to slow (to manny rows) use array's:
- Set sheet1 in an array and put the results in an other array.
- Dump the array with results in sheet2.

[sandy armstrong]

Thank Guido,
Thanks for the help on this Macro, This is what i was looking for but I would like to cut the Entire Row and paste it into sheet 2.

Option Explicit
Sub MoveYesToCompleted()
    Dim ShPendingNextRow As Long
    Dim ShPendingLastRow As Long
    Dim ShCompletedBlankRow As Long
 
    ShPendingLastRow = Worksheets("Pending").Cells(Rows.Count, "A").End(xlUp).Row
 
    For ShPendingNextRow = ShPendingLastRow To 6 Step -1
        If Worksheets("Pending").Cells(ShPendingNextRow, "G").Value = "Req. Feed Set Up" Then
            ShCompletedBlankRow = Worksheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row + 1
            With Worksheets("Pending")
                .Rows(ShPendingNextRow).Cut Destination:=Worksheets("Completed").Range("A" & ShCompletedBlankRow)
                .Rows(ShPendingNextRow).EntireRow.Delete
            End With
        End If
    Next
End Sub

I found this code on another fourm and it works great on a dummy file but then when i try to move it over to my real file it does not work.
I think its because the other file is to large I might need to change it into array and filter then dump results into sheet2 (just like you said) and i would like if this is possible to search on 2 sheet in my really file called sheet1 and sheet2 the dump results in in "Active" worksheet. Thanks Guido!!!! your the best...

[sandy armstrong]

Hey Guido,
I got it to work on my real file but for some reason it dosent grab all of them??? i dont know why

[Guido Geurs]

Attached is a standard tool to search rows.

[sandy armstrong]

Wow Guido thanks for this it looks like so much I’m a little intimidated to use this. How do I transfer the tool into the worksheet that I going to use this in. what I do is that is just copy and paste everything into the correct workbook, I just would like to know if there is an easier way to do it.
Thanks for your help...

[Guido Geurs]

No need to copy each time the code to the workbooks!
Just open the tool workbook with the form and open also the workbook in which you want to use the form.
Run in the second workbook the macro from the tool workbook with =
Start the User Form with the macro=
'UF Copy Rows.xls'!Start_Copy_Rows.

[sandy armstrong]

Ohh okay thanks. I guess i was doing it the really long and dum way thanks guido...