Hello All, hoping you can help with a problem I am having. I am very new to VB and macros so please bear with me as I try to
explain. I found this code online and tweaked it for what I need. But I need to expand on it further and don't know how to
add to the necessary values in the code.
I have a task list in excel, the entries are made in rows, and I am using
the following code on Sheet1 to copy row entries in which a Cell (always C, C4, C5, C6, ect...) is marked "Completed", to the
next available row on Sheet3 in the same workbook, and then delete that copied row from Sheet1.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextrow As Long, i As Long
nextrow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
If Not Intersect(Target, Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)) Is Nothing Then
i = Target.Row
If Target.Value = "Completed" Then
Range(Cells(i, "A"), Cells(i, "J")).Copy Sheet3.Range("A" & nextrow)
Range("A" & Target.Row).EntireRow.Delete
Application.CutCopyMode = False
Application.ScreenUpdating = True
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I need is to make it so that when the same cell is marked "Completed with Compromise" it does the same thing as
above, but moves it to Sheet2. I tired adding if statements in there, and I can't get it to work. I know I will need to
specify another "nextrow =" and make it Sheet2, and if I insert another "if statement in there it will work somewhat, but I
get a runtime error 1004. And if I click "End" it will actually work, but this is obviously not as fluid as it needs to be.
Any help is greatly appreciated, excel and vb is new to me, and I love a challenge, but I've searched and
experimented for hours with this and can't get it working.