Solved: Cut and paste a row to another sheet depending on cell value

[Grace79]

Hello
I am new to this forum and to VBA.

I'm not sure if what I'm after can be done - I've got one worksheet (called "open") where each row is an idea. One of the columns shows the status of the idea, either open or closed.

Is it possible that when the status is closed, the row can be cut and pasted to an existing worksheet ie named "closed"? And also can the row be deleted so not to leave a gap on the "open" sheet?

Any help would be much appreciated.

Thanks
Grace

[mdmackillop]

Hi Grace,
Welcome to VBAX.
Here's some code and an example. It assumes that Open/Closed is listed in column B
Regards
MD

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And UCase(Target) = "CLOSED" Then
Target.EntireRow.Copy Sheets("Closed").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
Application.EnableEvents = True
End Sub

[/vba]

[mdmackillop]

Hi Grace,
Previous code and sample amended to correct typos.

[Grace79]

That is brilliant, thank you for your help

[mdmackillop]

HTH
MD

[Grace79]

Bumping from the depths to ask if attachments are removed after a certain amount of time?

After a break from doing this type of work, I am back to

I thought I would be able to figure out a similar code from the example but I think I need the attachment to make sense of it...

[Bob Phillips]

They do seem to be. I have tried looking at previous posts only to find the attachment dropped off. Not sure what the threshold is though