Hi, I'm really hoping someone can help with this problem- I've Googled it non-stop. I have an excel sheet where I would like a row to be copied from the 'open cases' worksheet into the 'closed' worksheet when the value 'closed' entered in column K. I have the following macro: Private Sub Worksheet_Change(ByVal Target As Range)

Dim C As Range

If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub

For Each C In Intersect(Target, Me.Range("K:K")).Cells

If C.Text = "Closed" Then

Dim otherSheet As Worksheet

Set otherSheet = Sheets("Closed")C.EntireRow.Cut Worksheets("Closed").Cells(Rows.Count, "D").End(xlUp).Offset(1).EntireRow

End If

Next

End SubThis pastes the row but deletes any information which was is in the 'Closed' worksheet. Any help would be brilliant. Thank you,

My knowledge of VBA is just above nil, but wouldn't it better to CopythenCut?You might try changing the line that reads:

C.EntireRow.

CutWorksheets("Closed").Cells(Rows.Count,To something like:

C.EntireRow.

CopyWorksheets("Closed").Cells(Rows.Count,See if that works for you.

MIKE

First, a posting tip: Please click on the

blue lineat 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) End If End If End Sub

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 forTarget.Column = 11Just test for

Target = "Closed"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History