PDA

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



Grace79
03-06-2006, 09:41 AM
Hello
I am new to this forum and to VBA. :help

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
03-06-2006, 10:48 AM
Hi Grace,
Welcome to VBAX.
Here's some code and an example. It assumes that Open/Closed is listed in column B
Regards
MD


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

mdmackillop
03-06-2006, 04:47 PM
Hi Grace,
Previous code and sample amended to correct typos.

Grace79
03-07-2006, 01:52 AM
That is brilliant, thank you for your help :bow:

mdmackillop
03-07-2006, 10:13 AM
HTH
MD

Grace79
02-16-2010, 04:06 AM
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 :banghead:

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
02-16-2010, 04:13 AM
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