I am trying to copy paste selected rows from sheet "Request" to sheet "Month end console". If "YES" is mentioned in J column of the row, that row should be transferred to "Month end console"
Below is the code I tried. This works correctly until a row is met which is not mentioned as "YES" in J column. Once "NO" row is met, the next immediate YES row is pasted with the previous NO row's data. Have attached the workbook herewith. Much appreciate kind support from you all!
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("J:J")) Is Nothing Then Exit Sub If Target.Cells.Count > 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Target.Value = "NO" Then Exit Sub If Target.Value = "YES" Then Target.EntireRow.Copy Sheets("Month end console").Range("A" & Rows.Count) _ .End(xlUp).Offset(1, 0) Target.Select MsgBox "Data transfer complete!", vbExclamation Sheets("Month end console").Select End Sub