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

JohnTester

New Member
Joined
Sep 25, 2018
Messages
3
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,172
Try this macro in the worksheet code module.
VBA Code:
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

New Member
Joined
Sep 25, 2018
Messages
3
Try this macro in the worksheet code module.
VBA Code:
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.
 

Attachments

  • Excel.png
    Excel.png
    92.4 KB · Views: 0

JohnTester

New Member
Joined
Sep 25, 2018
Messages
3
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,172
This version will delete the row from the original sheet.
VBA Code:
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?
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,293
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top