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)
- End If
- End If
- End Sub
Thanks for any help on this.