|I don't see how your latest version of the code works for you. Unless I am entering something incorrectly, it doesn't do what I think you want.|
I created a sheet named SCARD. I entered this in R1:R2:
In K6 I entered 7/4/2013, which I assume is a valid date.
I went into the VBA editor and pressed F8 to single step through the code.
Since the date in K6 is greater than the last date in Column R, this statement is FALSE:
If Sheets("SCARD").Range("K6") <= Sheets("SCARD").Range("R" & lastDateRow) Then
Since that statement is FALSE, the code jumped down to the End If before the End Sub. Nothing was copied and the sheet remained Unprotected.
Is it me or is it the code?
Two other points:
1 - You are Unprotecting the sheet before you even check the date. It's a minor issue, but I prefer not to Unprotect a sheet until I am sure that I am going to write something to it. I then Protect the sheet immediately after the changes are made. Why waste the resources to Unprotect and then Protect the sheet if the input is such that you aren't going to use it?
My guess is that once you get the code working, you will only need one Protect instruction, not the 2 that you have now.
2 - If the date entered is invalid, you present a MsgBox that tells the user to "...Please Try Again." When the user clicks OK, you present another MsgBox telling the user to "Please re-enter the Correct Date". Personally, I don't see the need to tell the user twice that he needs to enter a different date.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.