Assumes column B is where date is entered on your input sheet.

This code will copy the entire row to sheet "returned" and delete the record
from the input sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Range
Set lastrow = Sheets("returned").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
On Error GoTo stoppit
Application.EnableEvents = False
With Target.EntireRow
.Copy Destination:=lastrow
.Delete Shift:=xlUp
End With
End If
stoppit:
Application.EnableEvents = True
End Sub

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

Copy/paste into that module. Edit to suit then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP

On Thu, 11 Sep 2008 11:34:02 -0700, Jugglertwo
wrote:

I thought that I would go to the real gurus to get the best answer for this
one.

I’ve been told that there is a way to make an entire line (record)
automatically move to another worksheet in the same workbook when you type in
a certain cell (example of how I would use it below).

I have the Lending Library to keep track of. I fill in information when
materials are sent out to keep track of who has materials out, what those
materials are and when they are due back. When the materials are returned, I
enter the date of return and then move it to the ‘returned’ worksheet. When I
fill in the return date, I would like to have the entire line move to the
returned spreadsheet automatically.

I assume this would be done with a macro. Would anyone be able to direct me
to a site that would help me get this macro code? I use the Macro Recorder
and know how to copy code into a modular but that is about it when it comes
to code.

Any assistance would be greatly appreciated.
Thanks!

Jugglertwo