Automatically moving row of data to another sheet

[drscfc]

Hi all

Am learning as I go with excel and have found this forum very useful for some of my little queries about formulae without having to post but am a bit stuck on what I want to do next.

I have a 2 worksheets set up - "Weekly Detentions" and "Archived detentions", Weekly detention is the sheet in which data is input. Columns A-N contain various drop down lists and one macro in column E. What I would like to happen is that when someone inputs a "y" in column L, the whole row of data is removed and put into "Archived Detentions" so that it forms a list of all the detentions that have been sat and the Weekly Detentions only contains those detentions that are yet to be sat.

Is this possible?

Any help would be hugely appreciated. Thanks.

 

[venkat1926]

right click the tab of the sheet "Weekly Detentions" and click view code
in the window that comes up copy paste this event code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> Range("L1").Column Then Exit Sub
If Target.Value = "y" Then
Target.EntireRow.Copy
Worksheets("Archived detentions").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End If
Application.EnableEvents = True

End Sub

 

[drscfc]

Thanks for the message

Did as directed but got no luck as of yet.

When I complete the action of y in column L I am getting an error message

Compile Error:
Ambigious Name Detected: Worksheet_Change

 

[venkat1926]

where did you park this code?

 

[drscfc]

Went into the view code of Weekly Detentions like you said and pasted it below the Macro that is running in row E for automatic date input

So code for whole worksheet looks like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("D:D").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "E").Value = Now
Else
Cells(Cell.Row, "E").Value = ""
End If
End If
Next Cell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> Range("L1").Column Then Exit Sub
If Target.Value = "y" Then
Target.EntireRow.Copy
Worksheets("Archived detentions").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End If
Application.EnableEvents = True
End Sub

 

[FishingIsMoreFun]

You cannot have two subs of the same name.

 

[VoG]

You need to combine them

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.EnableEvents = False
For Each Cell In Target
    If Cell.Column = Range("D:D").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "E").Value = Now
        Else
            Cells(Cell.Row, "E").Value = ""
        End If
    End If
Next Cell
If Target.Column <> Range("L1").Column Then Exit Sub
If Target.Value = "y" Then
    Target.EntireRow.Copy
    Worksheets("Archived detentions").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End If
Application.EnableEvents = True
End Sub

 

[drscfc]

Ok I see the problem with having two the same name, however have pasted that new code over the top and now neither macro seem to be working? No error messages or anything but no action taking place either. The automatic date is no longer appearing and nothing is copying across when column L is turned to y

Apologies for my ignorance but your helps is very much appreciated

 

[VoG]

Save your work, close and restart Excel. Does it work now?

 

[venkat1926]

open vb editor. click view and click immediate window
in the immediate window type
application.enableevents=true
and hit enter key.

now try to type y in the column L and see what happens.