Solved How to use to macro insert row into new worksheet in Excel.

January 5, 2016 at 05:31:46
Specs: Windows 8
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

End Sub

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

See More: How to use to macro insert row into new worksheet in Excel.

Report •

January 5, 2016 at 09:39:23
My knowledge of VBA is just above nil, but wouldn't it better to Copy then Cut?

You might try changing the line that reads:

C.EntireRow.Cut Worksheets("Closed").Cells(Rows.Count,

To something like:

C.EntireRow.Copy Worksheets("Closed").Cells(Rows.Count,

See if that works for you.


Report •

January 5, 2016 at 13:09:15
✔ Best Answer
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)
    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 for

Target.Column = 11

Just test for

Target = "Closed"

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

Report •

Related Solutions

Ask Question