HELPP!! Simple MACRO help! [Solved/Closed]

Ask a question lsn11 - Last answered on Jul 26, 2016 at 08:18 PM by vcoolio

I would like to create a macro that will scan sheet one for cells that contain the word "Complete", Copy the whole row, and paste it into another sheet (Sheet 2 - "Complete") without overwriting the rows in sheet 2. And Finally Deleting the copied "Completed" rows from sheet one.

I am having trouble finding a select row by certain text feature to start the macro with.


Text Word "Completed"
Sheet one name - "All PO'S"
Sheet two name - "Completed"
See more 
plus moins
Post some code with the steps you are stuck with. We can help you with broken code! We cannot provide a FREE turn key solution for your business! We are volunteers, and help when you need help!

Record a macro and post it here, start with that!
lsn11- Jul 25, 2016 at 04:34 PM
Hi! I completely understand. This is a macro formula I found in a similar forum I messed around with but does not work.

Sub Macro1()
Dim LR As Long
Range("A2").EntireRow.Insert Shift:=xlDown
LR = Sheets("ALL PO'S").Cells(Rows.Count, "C").End(xlUp).Row
LR1 = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Data").Range("C2:C" & LR)
.AutoFilter Field:=1, Criteria1:="ERROR", _
Operator:=xlOr, Criteria2:="MISSING"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Errors").Range("A" & LR1)
End With
End Sub
plus moins
Hello Isn11,

The code you have doesn't quite deal with the description you gave in your opening post.

1) You do not appear to be wanting to insert rows. This would not be relevant in your case.

2) The criteria you stated as "Complete" (or is it "Completed"?). The code above has two criteria: "ERROR" and "MISSING" which appear to be irrelevant to your case.

Hence, I would say that the following amended code may be more relevant to your query:-

Sub MoveIt()

  Dim lr As Long

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
With Range("C1", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Completed"
        lr = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
        If lr > 1 Then
        .Offset(1).EntireRow.Copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    End With
    .AutoFilterMode = False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The "ActiveSheet" is your main or input sheet ("ALL PO'S") from where the code will be run.

I've assumed that the criteria is in Column C as per the code that you supplied.

See how it works out for you and let us know your thoughts.

plus moins
Hello Isn11,

Sadly, you have cross-posted and not advised anyone that you have done so.

We consider that between this forum and the other in which you have posted, you have enough information to resolve your query by yourself.

Please familiarise yourself with the rules of any forum before signing up.

Hence, this thread will be closed and marked as solved.


Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!