Moving a Row when a condition is met

[lolly81]

Hi

Total Newbie here and do not have a clue, so thank you in advance for any help you can give . I need to move a row to another part of spreadsheet when a condition is met, and when it pasted below it will go into the next blank row.

So once the status column reads Improvement met- i need the row to be pasted below on a similar blank table and this row cleared. Then this cycle to continue so that the tabe below its constantly added too, and the top only contains outstanding improvements. I hope this make sense

I have never done coding before so any help is much appreciated

thank you

Concerns	Improvement Date	Status	Comments
Blah blah	01/01/2022	Improvement Met	Blah

 

[Jamie McMillan]

Hello Lolly,

I think by what you have said then you want to simply insert a new row.

The below macro does that - assuming 'Improvement Met' is in cell C2.

Sub MoveRow()
    If Range("C2").Value = "Improvement Met" Then
    Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End If
End Sub

I'll send instructions to run it automatically soon. I just have to look after my daughter now.

Jamie

 

[Jamie McMillan]

Hello Lolly,

Right click on the worksheet tab, select 'view code' - copy and paste the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Range("C2").Value = "Improvement Met" Then
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
End If
End Sub

That will insert a new row - move all the rows down.

No need for you to interact.

Jamie

 

[lolly81]

thank you all for your help

 

[Jamie McMillan]

Hello Lolly,

Since you are a new member; if the solution works then if you mark the best answer with the tick/checkmark on the righthand side. Then others will see the solution if they have a similar answer; and nobody else needs to spend time answering it.

Glad to have helped.

Jamie