VBA to move certain columns from a row to another sheet

[smittymj]

Hi! I would really appreciate help in building a code that would :

1. In Sheet 1, if any cells in Column E contains the word "Done" it would cut Column C of that row to the last column with data in the same row
2. Paste the entire row that was cut as values into the next free row in Sheet 2
3. Then delete the now blank row (entire row) in Sheet 1
   

Thinking of just assigning the macro to a button but I'd like to know too if macros can be made to work automatically. Thank you!

 

[mumps]

Do you want to paste the row to column A of Sheet2 or to column C?

 

[mumps]

I forgot to ask if the last column with data is fixed or can it vary? If it is fixed, what is the last column? If it varies, do all rows end at the same column or does that vary as well?

 

[smittymj]

I forgot to ask if the last column with data is fixed or can it vary? If it is fixed, what is the last column? If it varies, do all rows end at the same column or does that vary as well?

I pm'd you and wow you even replied before I sent my pm! I'd like to have it pasted started Column A of Sheet 2 and each row may end in varying columns.

I tried recording a macro and came up with this but doesn't really do what I'm trying to accomplish

Sub Macro2()
Cells.Find(What:="Done", After:=ActiveCell, LookIn:=xlFormulas, LookAt _        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.End(xlToLeft).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Range("A5").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Rows("3:3").Select
    Range("B3").Activate
    Selection.Delete Shift:=xlUp
End Sub

What could I do with it?

 

[mumps]

If you want to do this automatically without having to run a macro manually, then copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Done" in column E of any row and exit the cell. The data in that row will be automatically copied to Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim lColumn As Long
    If Target = "Done" Then
        lColumn = ActiveSheet.Cells(Target.Row, Columns.Count).End(xlToLeft).Column
        Range("C" & Target.Row).Resize(, lColumn - 2).Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[smittymj]

When I tried to make it automatic, it gave a "Run-time error '9': Subscript out of range' and highlighted this line

Range("C" & Target.Row).Resize(, lColumn - 2).Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0)

I also tried using Insert Module. I can't see the macro from the list of macros for both. What am I likely doing wrong?

 

[mumps]

Do you have a sheet named "Sheet2"?