Forum Discussion
Moving Rows from table on One sheet to table on other Sheet based off string value
I was wondering if a macro would work when I am trying to move a whole row from a table in sheet one to a table on sheet two If column B has a value of "Finished". So the clarify I have two sheets. and a table in each sheet = Sheet one with table one & sheet two with table two and if table one column B has value of FINISHED then that row moves to sheet two table two. Also the tables have hundreds of rows is there a certain way it has to be written to make sure it appears without over riding anything in the table its being added too?
4 Replies
Juliano-Petrukio Hello, I tried the link and whenever I add ByVal Target As Range in the function the macro disappears as one of my macro's and cannot be run. Here is my code below.
Private Sub MoveBasedOnValue(ByVal Target As Range)
'Move row to new spreadsheet
On Error Resume Next
Application.EnableEvents = False
'If Cell that is edited is in column B and the value is completed then
If Target.Column = 2 And Target.Value = "COMPLETED - ARCHIVE" Then
'Define last row on completed worksheet to know where to place the row of data
LrowCompleted = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row
'Copy and paste data
Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("Completed").Range("A" & LrowCompleted + 1).xlPasteValues
'Delete Row from Project List
Range("A" & Target.Row & ":B" & Target.Row).Delete xlShiftUp
End If
Application.EnableEvents = True
End Sub- Please remember that this is a worksheet event driven.
So your code must refer to Worksheet_Change event.
