|First, a posting tip: |
Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Using the pre tags will maintain the indents shown in the VBA editor.
That said, I'm confused as to why you are using a For-Next loop to copy what appears to be a single row. Of course, I don't know your process, but if you want the row moved to another sheet "when the value 'closed' entered in column K", then why not just move the Target row? It almost seems like you are "looping" through a single row. Do I have that wrong?
As for as the fact that it "deletes any information which was is in the 'Closed' worksheet" I am not seeing that happen. In my tests it continually pastes the "Closed" row to the next empty row in the Closed sheet.
Could it be that you are determining the next empty row by using Column D, not Column K? If Column D is empty, the next empty row will always be 2. Since you are moving the row based on an entry in Column K, we know that there will always be data in Column K ("Closed") in the Closed sheet once the paste is done. Therefore, that is the "safest" column to use to determine the next empty row.
If all you are doing is moving the row after the user enters Closed in Column K, this might work better for you:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then
If Target = "Closed" Then
nxtRw = Worksheets("Closed").Cells(Rows.Count, 11).End(xlUp).Row + 1
Target.EntireRow.Cut Worksheets("Closed").Cells(nxtRw, 1)
BTW, it may not be safe, but if the only place you will be entering the word "Closed" in a cell is when you enter it in Column K, you don't even have to test for
Target.Column = 11
Just test for
Target = "Closed"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.