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!

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!

Moving a row from one sheet to another
 

Absolutely brilliant - it works like a dream. Thank you so much!

Moving a row from one sheet to another
 

Absolutely brilliant - it works like a dream. Thank you so much!