VBA for date stamping certain cells & moving Rows to new sheet once completed

[JohnTester]

Good day.

I have a workbook utilizing columns A to S.
Column C time and date stamps any changes made in Column D
Column I for J and Column L for K.
Once all the needed information is captured I select between Yes or No in Column S.
Should it reflect "Yes" in Column S, the entire row must be moved to the Tab labeled "POD"

I have been searching the internet and found some Macros. The problem is that I cannot get them to work. (little knowledge about this stuff)

Below are the current Macros I am using, which gives only errors.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("D:D"), Target)
xOffsetColumn = -1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If

Set WorkRng = Intersect(Application.ActiveSheet.Range("J:J"), Target)
xOffsetColumn = -1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

Set WorkRng = Intersect(Application.ActiveSheet.Range("K:K"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
Sub Cheezy()
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Sales Orders").UsedRange.Rows.Count
    J = Worksheets("POD").UsedRange.Rows.Count
    If J = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("POD").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Sales Orders").Range("S1:S" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "YES" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("POD").Range("A" & J + 1)
            xRg(K).EntireRow.Delete
            If CStr(xRg(K).Value) = "YES" Then
                K = K - 1
            End If
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
End If
End Sub

 

[mumps]

Try this macro in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D,J:J,K:K,S:S")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 4, 10
            Target.Offset(, -1) = Format(Date, "dd-mm-yyyy")
        Case Is = 11
            Target.Offset(, 1) = Format(Date, "dd-mm-yyyy")
        Case Is = 19
            If Target = "Yes" Then
                Target.EntireRow.Copy Sheets("POD").Cells(Sheets("POD").Rows.Count, "A").End(xlUp).Offset(1)
            End If
    End Select
    Application.ScreenUpdating = True
End Sub

If I understood correctly, this macro should do everything you requested. A change in column D will insert a date stamp in column C, a change in column J will insert a date stamp in column I, a change in column K will insert a date stamp in column L and the selection of "Yes" in column S will automatically copy the row to "POD".

 

[JohnTester]

Try this macro in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D,J:J,K:K,S:S")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 4, 10
            Target.Offset(, -1) = Format(Date, "dd-mm-yyyy")
        Case Is = 11
            Target.Offset(, 1) = Format(Date, "dd-mm-yyyy")
        Case Is = 19
            If Target = "Yes" Then
                Target.EntireRow.Copy Sheets("POD").Cells(Sheets("POD").Rows.Count, "A").End(xlUp).Offset(1)
            End If
    End Select
    Application.ScreenUpdating = True
End Sub

If I understood correctly, this macro should do everything you requested. A change in column D will insert a date stamp in column C, a change in column J will insert a date stamp in column I, a change in column K will insert a date stamp in column L and the selection of "Yes" in column S will automatically copy the row to "POD".

Thank you, it is almost perfect.

It only populates onto row 27 and not further than that.
Also needs to delete the row from the original sheet if possible.

 

[JohnTester]

Thank you, it is almost perfect.

It only populates onto row 27 and not further than that.
Also needs to delete the row from the original sheet if possible.

I have found the cause for 27 Row populating. Data is needed in Column A when changing column S to yes.

 

[mumps]

This version will delete the row from the original sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D,J:J,K:K,S:S")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 4, 10
            Target.Offset(, -1) = Format(Date, "dd-mm-yyyy")
        Case Is = 11
            Target.Offset(, 1) = Format(Date, "dd-mm-yyyy")
        Case Is = 19
            If Target = "Yes" Then
                Target.EntireRow.Copy Sheets("POD").Cells(Sheets("POD").Rows.Count, "A").End(xlUp).Offset(1)
                Rows(Target.Row).Delete
            End If
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I have found the cause for 27 Row populating. Data is needed in Column A when changing column S to yes.

Have you fixed this problem?