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?

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, 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"

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

18 Replies

Wyn Hopkins

MVP

Apr 30, 2019

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

neilbicknell

Copper Contributor

Apr 30, 2019

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

Date	Name	Status
10-Jan	Bob	New
10-Feb	Sue	Closed
12-Feb	Jan	New
13-Dec	Frank	New








			DATA
			New
			Closed
			Dead

Wyn Hopkins
MVP
Apr 30, 2019
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.

Forum Discussion

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

18 Replies

Resources