Clarkinkent

Estimable
Feb 26, 2014
2
0
4,510
Hello guys,

I have a workbook and I need to find the NO values on ROW G (Row 7) and then copy the line that NO belongs to a new sheet (TAB) called summary, in my case it is listed as sheet 18.

I need to search on all sheets though from Sheet 1 to Sheet 17 in their G Rows for NO's.

I have a code I have found online and amend it to work with my criteria. But it does not seem to work as I would like it to it keeps coming up with errors.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRow As Integer
'Determine if change was to Column G (7)
If Target.Column = 7 Then
'If Yes, Determine if cell = NO
If Target.Value = "NO" Then
'If Yes, find next empty row in Sheet 18
nxtRow = Sheets(18).Range("F" & Rows.Count).End(xlUp).Row + 1
'Copy changed row and paste into Sheet 18
Target.EntireRow.Copy _
Destination:=Sheets(18).Range("A" & nxtRow)
End If
End If
End Sub

Thank you in advance.
Vasilis.