Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a row from one sheet to another
Hi, I am trying to create a spreadsheet whereby the input of
information into a cell on a row will cause the data from that row to be transferred to another sheet. The workbook I am creating is a log for warehouse/ stock discrepancies. The first sheet will contain all the discrepancies as they arise - with a column titled "resolved by" where the person will enter their initials once they have resolved the problem. As soon as this cell is completed, I want that entire row to transferred to the next available row on a "resolved discrepancies" sheet, and to be deleted from the first "discrepancies" sheet. I hope this makes sense! Any help would be much appreciated. I have some limited VBA knowledge but this is beyond my capabilities! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a row from one sheet to another
You would use the change event.
See Chip Pearson's site on events: http://www.cpearson.com/excel/events.htm You would need to put in an if statement to see if the change was to the column for initials (assume column F) Target holds a reference to the cell that triggered the change Private Sub Worksheet_Change(ByVal Target As Range) Dim rng as Range if target.count 1 then exit sub On Error goto ErrHandler If target.column = 6 then ' is it column F if not isempty(target) then ' has initials Application.EnableEvents = False ' find row to copy to on sheet named DestSheet set rng = worksheets("DestSheet") _ .Cells(rows.count,1).end(xlup)(2) ' do the copy Target.entirerow.copy rng ' delete the row Target.entireRow.Delete end if End if Errhandler: Application.EnableEvents = True End Sub You would right click on the sheet tab of the sheet containing the data where the initials would be placed and select view code. then put in code like the above in the resulting module. Obviously you should test this rigorously on a copy of your workbook so you don't destroy your data. -- Regards, Tom Ogilvy "ell7" wrote: Hi, I am trying to create a spreadsheet whereby the input of information into a cell on a row will cause the data from that row to be transferred to another sheet. The workbook I am creating is a log for warehouse/ stock discrepancies. The first sheet will contain all the discrepancies as they arise - with a column titled "resolved by" where the person will enter their initials once they have resolved the problem. As soon as this cell is completed, I want that entire row to transferred to the next available row on a "resolved discrepancies" sheet, and to be deleted from the first "discrepancies" sheet. I hope this makes sense! Any help would be much appreciated. I have some limited VBA knowledge but this is beyond my capabilities! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a row from one sheet to another
Absolutely brilliant - it works like a dream. Thank you so much!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving a row from one sheet to another
Absolutely brilliant - it works like a dream. Thank you so much!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for moving information from one sheet to another sheet | Excel Discussion (Misc queries) | |||
moving from one sheet to another from a cell of a sheet | Excel Worksheet Functions | |||
moving informatiion from one sheet to a total sheet | Excel Discussion (Misc queries) | |||
moving around a sheet | Excel Programming | |||
Moving sheet | Excel Discussion (Misc queries) |