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)
- MsgBox "Data transfer complete!", vbExclamation
- Sheets("Month end console").Select
- End Sub