Thread: Combining two Private Sub Worksheet_Change

I'm having some issues here. I have two Private Sub Worksheet_Change that I need in one worksheet. I can't figure out how to make it work with both private subs. Below is my coding. Any ideas would be so helpful!!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' stanleydgromjr, 09/05/2011
' Version 3, after copying the Target.Row, delete the Target.Row
' http://www.excelforum.com/excel-programming/790860-move-entire-row-to-another-worksheet-based-on-cell-value.html
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim P As Long, NR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target.Value
    Case "Booked"
      P = Application.Match("Potential", Worksheets("Booked").Columns(4), 0)
      NR = Worksheets("Booked").Range("D" & P).End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("Booked").Range("A" & NR)
      Rows(Target.Row).Delete
    Case "DNMQ"
      P = Application.Match("Potential", Worksheets("DNMQ").Columns(4), 0)
      NR = Worksheets("DNMQ").Range("D" & P).End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("DNMQ").Range("A" & NR)
      Rows(Target.Row).Delete
  End Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Private Sub Worksheet_Change1(ByVal Target As Range)
    SetDateRow Target, "G"
     'or
     'SetDateCol Target, 5
End Sub
 
Sub SetDateRow(Target As Range, Col As String)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Col) = Int(Now())
    Application.EnableEvents = True
End Sub
 
Sub SetDateCol(Target As Range, Rw As Long)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Cells(Rw, Target.Column) = Now()
    Application.EnableEvents = True
End Sub

That code didn't work for me. Let me tell you what it needs to do because the current code can be re-written probably. I'm not sure if its the most effective way or if its compatible with eachother.

I'm going to attach the document and then explain what I'm trying to have happen.

1) worksheet named current: when I change the status in status column to Booked - the entire row will be deleted and moved to the Booked worksheet.
2) worksheet named current: when I change the status in the status column to DNMQ - the entire row will be deleted and moved to the DNMQ worksheet.
3)worksheet named current: when I change the status in the status column to Application, I want todays date to appear in the App Date column (G). I don't want this date to change each time its updated or saved though. A static date if you will. Also, if I change from Approved to closing I dont want the date to change in the column G. Once its there I don't need it to change for tracking purposes.
4) Finally, when there is a change in columns A-F I want another static date to appear in column J. This date wont change until their is any change in columns A-F.

Any help or ideas would be so appreciated.

Right...? That's what I'm trying to figure out how to do. Not disputing the fact that there can be only one change event.

Originally Posted by Leith Ross

Hello bdf0827,

There is and always will be one and only one Worksheet Change event per worksheet. All the code therefore must be combined into the one event module.

It didn't work as in it did nothing. And I'm trying to learn this stuff. It's hard having to filter through the sarcasm everyone seems to post.

Originally Posted by shg

That's not very helpful. Did it do nothing? Something, but not what you wanted? In what way?

Are you looking for someone to code to your specifications (aka, a consultant), or someone to help you help yourself?

Really, Roy? Can you not read? I said it didn't work. Stop trolling. Troll!

Originally Posted by royUK

Find another forum then

You haven't even used the suggested code