Cut cells and move

[satnair]

Hi,

I have a spreadsheet, and in one of the columns is a status of the request, once the status changes to closed the entire cell needs to be copied and moved to the last row which has the closed status. This would ideally mean that only open tickets with all other status except closed would be listed below.

Two things that need to be accomplised:
1) The entire Row colous should change
2) It should cut the rows and paste it to the last row which has closed.

Can this be done.

I have attached a sample file for reference.

Thanks

[mgm05267]

Hi Satnair,

Changing entire row color when status changed is possible by Conditional Formatting.....

But why cutting & pasting is required to be in the last......

Anyway.... if color for entire row is changed.. you can either filter it out based on closed status or color basis....

Regards,

MGM

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If .Column = 8 Then

If .Value = "Closed" Then

Me.Rows(Target.Row).Cut
Me.Rows(Me.Cells(Me.Rows.Count, "A").End(xlUp).Row + 1).Insert
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[satnair]

Thanks,

I will check on this and revert for help if required.

Bye.

[satnair]

Hi,

This works fine, only one issue is that if there are new request after the closed ones then this doesnot fall in line and the closed will be at the end.

Can this be move to another worksheet may so that all the closed tickets are reflected in another worksheet and only the other incomplete ones remain on the active sheet.

Thanks for help on this one.

Attached the file for reference.



Satnair

[satnair]

Any help coming on this request please....

Rgds.