Help with Auto Archiving Data

[jdodge92]

Hi,

I am trying to set my worksheet to automatically move a row from one Sheet "Awaiting Collection" to another Sheet called "Archive" when a "Y" is entered in to a cell in Column G. I would like the data to then be removed from the Sheet "Awaiting Collection" once it has been moved on to the Sheet "Archive".

I have been trying various different codes that I have found on other posts on this forum and from other sources but I cant seem to get any to do exactly what I want when I change to suit my set up.

If anyone would be able to point me in the right direction then that would be great!

Many Thanks,
Jordan

 

[John_w]

Try this in the sheet module of the "Awaiting Collection" sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
    
    With Target
        If .Rows.Count = 1 And .Column = Columns("G").Column Then
            If .Value = "Y" Then
                lr = Worksheets("Archive").Cells(Rows.Count, "A").End(xlUp).Row
                .EntireRow.Copy Worksheets("Archive").Cells(lr + 1, "A")
                Application.EnableEvents = False
                .EntireRow.Delete
                Application.EnableEvents = True
            End If
        End If
    End With
    
End Sub

 

[jdodge92]

Try this in the sheet module of the "Awaiting Collection" sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
  
    With Target
        If .Rows.Count = 1 And .Column = Columns("G").Column Then
            If .Value = "Y" Then
                lr = Worksheets("Archive").Cells(Rows.Count, "A").End(xlUp).Row
                .EntireRow.Copy Worksheets("Archive").Cells(lr + 1, "A")
                Application.EnableEvents = False
                .EntireRow.Delete
                Application.EnableEvents = True
            End If
        End If
    End With
  
End Sub

[/QUOTE]

Perfect!! That's worked a dream!

Thanks Very Much
Jordan