Thanks so much. Works brilliantly :-)
Although I had to change it very slightly as I took on board your points
about accidentally deleting data and I added another column with a dropdown
blank/Yes to trigger the script.

Kev

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "I:I"
Dim cell As Range
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Target.EntireRow
Set rng2 = Worksheets("Awaiting outcome").Cells _
(Rows.Count, 1).End(xlUp).Offset(1, 0)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Value < "" Then
With rng1
.Copy Destination:=rng2
.Delete Shift:=xlUp
End With
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the Awaiting Allocation sheet tab
and "View Code".

Copy/paste the code into that sheet module.

Alt + q to return to Excel and start entering values into column I


Gord Dibben MS Excel MVP



On Thu, 17 Dec 2009 08:51:01 -0800, KevHardy
wrote:

Hi,
I have a workbook with two sheets. Both use columns A to I.
What I would like to do is:
1) When a vaue is entered into column I (i.e. it is no longer empty) the row
A to I is copied to the next available row on the second sheet.
2) the original data is removed from sheet 1.

The project is a list of work awaiting allocation to a particular worker on
an "Awaiting Allocation" list. When the work is allocated to someone I want
the information deleted from the "Awaiting Allocation" sheet and added to the
second sheet "Awaiting outcome".

Any ideas :-)

.