Hello!

Part I: If there is an X in any row in column A in the range A8 to A17, copy the row columns B to P to the next available row in Sheet2. (Sheet2 has a header row.) (This part works.)

Part II: However, if the row to be copied has Column M = "Dog", then copy the row as-is (this part works), but then also make another copy of that same row, automatically changing "Dog" to "Cat". (This part doesn't work.)

Part III: Sheet 2 can only contain a max of 10 copied rows. (This part doesn't work if row to be copied is the tenth and contains "Dog".)


I have a working macro for Part I, but after hours/days of Googling, I can't make the leap to get Part II and III to work.

Part I:

Private Sub btnRowCopy_Click()
Dim ce As Range, NR As Long

   For Each ce In Range("A8:A" & Cells(Rows.Count, "A").End(xlUp).Row)
      If Not IsEmpty(ce) Then
        With Sheets("Sheet2")
            NR = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Row
            If NR >= 12 Then
                MsgBox "You already have 10 copied rows on Sheet2."
            Else
                .Unprotect "SECRET"
                .Cells(NR, "A").Resize(1, 17).Value = Range(ce, ce.Offset(0, 16)).Value
                ce.ClearContents
                .Select
                .Protect "SECRET"
            End If
        End With
      End If
    Next ce
End Sub
This is the last hurdle before this project can hit the streets, so any help appreciated.

Respectfully,

Lost