Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move to next record | Excel Discussion (Misc queries) | |||
Type set amount of digits in one cell then automatically move to n | Excel Worksheet Functions | |||
Type 3 digits in one cell then automatically move to next cell. | Excel Discussion (Misc queries) | |||
format many rows depending on record type | Excel Programming | |||
Macro to record date on sheet data is entered in a cell ? | Excel Programming |