VBA: Moving data range based on contents of specific cell in the range

[OJMcGee]

Hi,

I'm creating a 'to-do' list and currently have 6 columns.

Columns A:C are for 'to-do' items:
Column A: Task
Column B: Priority
Column C: Status

Columns D:F are for the 'doing' items
Column D: Task
Column E: Priority
Column F: Status

There are 20 rows.

When a 'to-do' task is updated to 'Doing', I would like for it to move over to the first available empty range in the 'Doing' section, i.e. in the image, A6:C6 would move to D6:F6 and A8:C8 would move to D7:F7, clearing them from the 'to-do' section.

 

[Legacy 143009]

Hello,

This should work:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C:C")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Value = "Doing" Then
      Range("D" & Cells(Rows.Count, "D").End(xlUp).Row).Resize(, 3).Value = Target.Offset(, -2).Resize(, 3).Value
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[OJMcGee]

Hello,

This should work:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C:C")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Value = "Doing" Then
      Range("D" & Cells(Rows.Count, "D").End(xlUp).Row).Resize(, 3).Value = Target.Offset(, -2).Resize(, 3).Value
    End If
    Application.EnableEvents = True
  End If
End Sub

This is a great start, thank you.

I made a slight change so that it was filling the 'Doing' section, instead of overwriting everything:

Range("D" & Cells(Rows.Count, "D").End(xlUp).Row+1).Resize(, 3).Value = Target.Offset(, -2).Resize(, 3).Value

How can I now add to this so that after it adds to the 'Doing' section, it removes that entry from the 'To Do' section?

 

[Legacy 143009]

Like this?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C:C")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Value = "Doing" Then
      Range("D" & Cells(Rows.Count, "D").End(xlUp).Row).Offset(1).Resize(, 3).Value = Target.Offset(, -2).Resize(, 3).Value
      Target.Offset(, -2).Resize(, 3).Delete Shift:=xlUp
    End If
    Application.EnableEvents = True
  End If
End Sub