Using VBA to archive a row to a new sheet (task list sheet)

[terraf]

Hello
I am sorry if this has been asked before, I am a new member and also rather new to excel!
I have a spreadsheet with 8 tabs along the bottom all with 'to do list' type entries. I'd like to make a new tab at the end called 'archive' where I can store all rows with completed tasks in, removing them from the other 8 tabs but transferring the data to archive.
Each tab has 4 columns, the final one being named 'archive' with a drop down list of yes or no. If I choose 'yes' in the 4th column I would like the whole row to transfer to the archive sheet.
Can anyone suggest a code that would work for this?
Hugely appreciated

 

[JLGWhiz]

You can do that with change event code. Copy and paste the code below into the ThisWorkbook code module of your workbook. when you make changes on your To Do sheets, the code will run automatically and if there is a "Yes" in column D, it will remove that row from the To Do sheet and put it in the next available row of the Archive sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Sh.Name = "Archive" Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Sh.Range("D:D")) Is Nothing Then
        If LCase(Target.Value) = "yes" Then
            Target.EntireRow.Cut Sheets("Archive").Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    End If
Application.EnableEvents = True
End Sub

 

[terraf]

Thank you very much! I will give this a try today!

 

[JLGWhiz]

Thank you very much! I will give this a try today!

You're welcome,
regards, JLG

 

[terraf]

It works, thanks again! I think it is magic!

Is there any way to add something in to delete the empty row once the data has been transferred to the 'archive' sheet?

Huge thanks

 

[JLGWhiz]

Use this modified version.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Sh.Name = "Archive" Then Exit Sub
Dim r As Long
Application.EnableEvents = False
    If Not Intersect(Target, Sh.Range("D:D")) Is Nothing Then
    r = Target.Row
        If LCase(Target.Value) = "yes" Then
            Target.EntireRow.Cut Sheets("Archive").Cells(Rows.Count, 1).End(xlUp)(2)
            Rows(r).Delete
        End If
    End If
Application.EnableEvents = True
End Sub

 

[Vavouyios]

Use this modified version.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Sh.Name = "Archive" Then Exit Sub
Dim r As Long
Application.EnableEvents = False
    If Not Intersect(Target, Sh.Range("D:D")) Is Nothing Then
    r = Target.Row
        If LCase(Target.Value) = "yes" Then
            Target.EntireRow.Cut Sheets("Archive").Cells(Rows.Count, 1).End(xlUp)(2)
            Rows(r).Delete
        End If
    End If
Application.EnableEvents = True
End Sub

Hi JLG,

This is great code! Can it be modified to do the same operation but instead take the row from a to-do list table and put it into another table (archive)? I tried running this code with tables and it properly removed and deleted it from the to-do list table and put it in the archive sheet but it put it below (or after) the archive table. Let me know if that is possible.

Best,
Alex

 

[JLGWhiz]

See if this will work:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Sh.Name = "Archive" Then Exit Sub
Dim r As Long, lr As ListRow
Application.EnableEvents = False
    If Not Intersect(Target, Sh.Range("D:D")) Is Nothing Then
    r = Target.Row
        If LCase(Target.Value) = "yes" Then
            Set lr = Sheets("Archive").ListObjects(1).ListRows.Add
            Target.EntireRow.Cut lr
            Rows(r).Delete
        End If
    End If
Application.EnableEvents = True
End Sub

 

[mymiliontree]

Hello,
I am working on a spreadsheet where I am tracking the contents of our warehouse racking. I've set up a 'database' sheet where each location in the racking uses a single row and is uniquely identified. When the data is entered via a userform, it uses the unique identifier to put it into the correct location on the database sheet. That part works fine.
What I want to do is add is the ability to archive a row to an archive sheet and delete it from the database sheet when the location becomes empty.
In a single row there is 10 columns of information to archive. I want to archive all ten items in the row but on the database sheet, only delete 9 of the items in a row as column one holds the unique identifier. I can delete the entire row on the database sheet but can't figure out how to maintain column one.
Does that make sense?
Any thougthts/help are greatly appreciated.

Thanks,