Excel 2010 VBA to Move rows in a sheet based on the project Status

 

neilbicknell 

 

First step is to name column C as StatusColumn in the name manager box  (this avoids you code breaking should someone insert a column to the left in the future)

 

 

 

Then put this code in your sheet1 VBA window 

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RowToRemove As Range

'avoid triggering code if New is typed in any other random cell expect for inside the StatusColumn

If Intersect(Target, Range("StatusColumn")) Is Nothing Then
    Exit Sub
End If



'avoid error if entire column is selected
If Target.Rows.Count > 1 Then
    Exit Sub
End If


'Relies on the idea that user will press Enter after typing "New" which shifts the cursor down
'and therefore the row above (offset -1) is the one to remove


If Target.Offset(-1, 0).Value = "New" Then

    Set RowToRemove = Selection.Offset(-1, 0)
       
       Sheet2.Range("A1").EntireRow.Insert
        
       RowToRemove.EntireRow.Copy Sheet2.Range("A1")
        
    RowToRemove.EntireRow.Delete
    
    Set RowToRemove = Nothing


End If

End Sub

This code does assume that the user will type New and press Enter to move the cursor down to the next cell.  Some users do change that Enter behaviour to jump right instead of down which will result in the wrong row being moved.

 

With time I'd investigate a more robust solution but this is the best I can do for now.

 

Hope it helps

 

Wyn

 

 

 

 

Ah, OK, that requires some more code. I'll get back to you after work tonight.

As soon as “New” is entered in column C, I want that row to move automatically to sheet 2. Thus disappearing from sheet 1 immediately

Hi
The code only works for me when I “run” it manually.

What is the trigger / event you want to make the code run? You mention you want the rows to move “based on their selection”. Can you explain what you mean
Thanks