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 :-)

I am not saying you should not do what you are intending but from my
experience it can be problematic. Once you enter something in I the data
disappears from the sheet you are on and is place on the other sheet. What
happens when (not if) you accidentally put something in I. How do you get the
item back. What if you type the wrong thing in I or wnder if you did. You
need to go to the other sheet to confirm.

Personally I would keep all of the data together on the one sheet and then
just filter on the results of I to get the info sorted out.
--
HTH...

Jim Thomlinson


"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 :-)

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 :-)

Very good points Jim


Gord

On Thu, 17 Dec 2009 09:12:05 -0800, Jim Thomlinson
wrote:

I am not saying you should not do what you are intending but from my
experience it can be problematic. Once you enter something in I the data
disappears from the sheet you are on and is place on the other sheet. What
happens when (not if) you accidentally put something in I. How do you get the
item back. What if you type the wrong thing in I or wnder if you did. You
need to go to the other sheet to confirm.

Personally I would keep all of the data together on the one sheet and then
just filter on the results of I to get the info sorted out.

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 :-)

.

Thanks for the feedback and thanks to Jim for the "be aware" points.


Gord

On Fri, 18 Dec 2009 07:26:02 -0800, KevHardy
wrote:

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 :-)

.