Move one record to another sheet when I type in a cell
 

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

Ive 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

Move one record to another sheet when I type in a cell
 

Hi,

Not too much detail but this may help. Right click the sheet tab where you
enter details of material out, view code and past this in. What it will do is
allow you to input values into any cell but as soon as you type in column E
(The return date) it will transfer the entire row the first empty row in a
sheet called "Returned" and delete the record from your input sheet. Change
column E to the one you want to initiate the change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("E:E")) Is Nothing Then
Application.EnableEvents = False
Target.EntireRow.Copy
Lastrow = Sheets("Returned").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Returned").Range("A" & Lastrow + 1).PasteSpecial
Application.EnableEvents = True
Target.EntireRow.Delete
End If
Application.CutCopyMode = False
End Sub

Mike

"Jugglertwo" wrote:

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

Ive 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

Move one record to another sheet when I type in a cell
 

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