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

Copper Contributor

I have found so many different codes for this it's ridiculous. I don't think it should be very complicated. I want to move an entire row of data in sheet 1 based on the status of column "C" in that row, in sheet 1 ,  to sheet 2. I want the rows in sheet 2 to be a running list- IE not keep overwriting. Can someone please help me?

18 Replies

Hi @neilbicknell 

 

Apologies this code is a bit rough, I'm not a fan of hardcoding cell references and counters but hopefully this gives you an idea and you can tweak it to suit 

 

Sub MoveToSheet2()


Dim i As Integer

Dim RowToRemove As Range


For i = 1 To 1000

    Sheet1.Range("C1").Offset(i, 0).Select

    If Selection.Value = "move" Then
    
       Set RowToRemove = Selection
       
       Sheet2.Range("A1").EntireRow.Insert
        
       Selection.EntireRow.Copy Sheet2.Range("A1")
        
       RowToRemove.EntireRow.Delete
    
        i = i - 1


    End If

Next i




End Sub

@Wyn Hopkins thank you but that code didn't do anything.

 

I need rows 2, 4 and 5 to move to sheet 2 based on the Status of "new" being selected. This will be selected by a drop down box list of data. please help as I don't really know anything about coding

 

 

DateNameStatus  
10-JanBobNew  
10-FebSueClosed  
12-FebJanNew  
13-DecFrankNew  
     
     
     
     
     
     
     
     
    DATA
    New
    Closed
    Dead
     
Ah Ok, so in the code you need to change the word "move" to "New"

Also the i = 1 to 1000 means the code will run for 1,000 rows. Increase or decrease this number as required.

I would really warn against using VBA code if you are not very familiar with it as it can lead to unintended errors.

@Wyn Hopkins yes I understood that much. I enter this code on sheet 1 and it does not do anything, having changed move to "New"

@Wyn Hopkins yes i understood this. The code did not work:(

@neilbicknell 

 

Could you send me a screenshot like this one

 

image.png

So when you run that code nothing happens?
That’s correct. Does the code work for you? Could the fact that I am running 2010 be a problem?
Works for me, should work on 2010

Make sure that "New" doesn't contain spaces at the start or end in column C of sheet 1

If you can attach a de-sensitised version of your file I'll take a look

@neilbicknell 

 

That works for me

 

I've added a button to run the code.

 

 

@Wyn Hopkins ugh I don't understand why the code doesn't work for me. Having the button works but I would rather not have to have the user press this. I would like the rows to automatically move based on their selection. Any other thoughts as to why it isn't working?

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
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
Ah, OK, that requires some more code. I'll get back to you after work tonight.

 

@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)

 

image.png

 

 

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