0

I have two worksheets ("Worksheet A" and "Worksheet B").

Worksheet A has a list of tests. Each row represent a test performed. Column F has the value "Passed" or "Not Passed".

If "Not Passed", the entire row needs to be added automatically to "Worksheet B" which represent an action list.

Any suggestion on how to do this dynamically, i.e. without having to run a macro manually?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Sal M.
  • 1
  • Obviously you could try the formula `=IF(Sheet1!$F1="Not Passed",Sheet1!A1,"")` in A1 of sheet B, and expand to your range, but this will leave blank rows between the entries on sheet B – bm13563 May 03 '19 at 12:38
  • 1
    Sounds like [worksheet events](https://stackoverflow.com/questions/15337008/excel-vba-run-macro-automatically-whenever-a-cell-is-changed) – JeffB May 03 '19 at 12:39
  • Its possible with , Evaluate , IndexMatch , but you need to try it first – Ronan Vico May 03 '19 at 13:09

1 Answers1

0

Use worksheet events. Alt + F11 to open VBA interface:

Double click "Sheet1" on the left hand side of the interface and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 0 Then
        update_sheet
    End If
End Sub

Below where it says "Sheet1", right click on "Modules". Hover on insert and click "Add Module". Paste the following code into the module:

Sub update_sheet()
    s1_rows = ThisWorkbook.Worksheets(1).Cells(ThisWorkbook.Worksheets(1).Rows.Count, "F").End(xlUp).Row
    Set s1_rng = ThisWorkbook.Worksheets(1).Range("F1:F" & s1_rows)

    s2_rows = 1
    For Each cell In s1_rng
        If cell.Value = "Not Passed" Then
            cell.EntireRow.Copy
            ThisWorkbook.Worksheets(2).Range("A" & s2_rows).PasteSpecial xlPasteValues
            s2_rows = s2_rows + 1
        End If
    Next cell
    Application.EnableEvents = True
End Sub

This can be improved, but should start you off.

bm13563
  • 688
  • 5
  • 18