Cut and Paste entire row to new worksheet based on criteria in 1 cell

**KS1986** · 09-20-2012, 05:24 AM

I currently have a piece of code run from a button which looks at the value in column 7 of a worksheet, if the column = "Completed" the code will then select the entire row and delete it.

What I would like the code to do instead is simply cut and paste the row to the next empty row of a new worksheet then delete the empty row left by the original.

Current code is:

For i = 2 To 199

If ActiveSheet.cells(i, 7).Value = "Completed" Then
cells(i, 7).EntireRow.Delete

i = 1 + i
End If
Next i

End Sub

All suggestions much welcomed
Many thanks
KS

**arlu1201** · 09-20-2012, 05:26 AM

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

**arlu1201** · 09-20-2012, 05:27 AM

You have not provided the initial part of your code, so is this code triggered by a worksheet change / selection change event? Or is the code triggered by a button?

**KS1986** · 09-20-2012, 05:31 AM

Sorry it is noted in my first line...it is triggered from a button.

Thanks

**arlu1201** · 09-20-2012, 05:37 AM

Oops my bad. Sorry.

Try this code

Option Explicit

Sub copy_completed()
Dim i As Long, lrow As Long

Application.ScreenUpdating=False

With Worksheets("Sheet1")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1
        If .Range("G" & i).Value = "Completed" Then
            .Rows(i).Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            .Rows(i).Delete
        End If
    Next i
End With

Application.ScreenUpdating=True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

**KS1986** · 09-20-2012, 05:55 AM

That's great, thanks for your help!

**arlu1201** · 09-20-2012, 05:56 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.