I have the following macro that I have for a Worksheet that is a Table. What I am trying to accomplish is to move any row that has the value of "Closed" in column N to another sheet called "Closed". As of now, the macro does copy the row, but there are 2 issues I have with it.
1. It does not delete the row for the originating sheet (gives run-time error '1004' Delete method of Range class failed.)
2. It copies all of the formatting with it. I want it to take on the formatting of the new sheet.
Neither worksheet is protected.
Here is the macro;
Private Sub Worksheet_Change(ByVal Target As Range) 'Version 2 If Not Intersect(Target, Range("N:N")) Is Nothing Then If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub Dim Lastrow As Long Lastrow = Sheets("Closed").Cells(Rows.Count, "N").End(xlUp).Row + 1 If Target.Value = "Closed" Then Rows(Target.Row).Copy Destination:=Sheets("Closed").Rows(Lastrow) Rows(Target.Row).Delete End If End If End Sub
Thanks for any help on this.