To Do List

Following macro moves the completed task(entire row) to Archive sheet 

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Application.EnableEvents = false
  3. On Error Goto ENEV
  4. REM add your code below
  5.  
  6.     Dim row_num as Integer          'Decleration
  7.  
  8.     If Target.column = 4    Then       ' Task status is in column D
  9.         If UCase(Target.value) = "COMPLETED"    Then
  10.  
  11.             row_num = Sheets("Archive").UsedRange.rows.count + 1        'Calculating next empty row in "Archive" sheet
  12.  
  13.             Target.EntireRow.Select
  14.             Selection.copy
  15.             Sheets("Archive").Cells(row_num,1).Select
  16.             Selection.PasteSpecial Paste:=xlAll, SkipBlanks:=False, Transpose:=False   'Copy pasting the task from "Task" sheet to "Archive" sheet
  17.  
  18.             Target.EntireRow.Delete     'Task from "Task" sheet is deleted
  19.  
  20.             Target.Select
  21.  
  22.         End If
  23.     End If
  24.  
  25. ENEV:
  26. Application.EnableEvents = true
  27. End Sub


    		 RSS of this page