Dec 05 2022 06:21 AM
Hello all,
I am a vba newbie and have found a code that helps to archive data when it is switched to completed. It all works well, but for the paste aspect of it I want it to add a row to the bottom of a table (I have yet to create) and paste it there. Not sure how to go about and tweak the code to add this function so any help would be greatly appreciated!
Here is the current code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject
Dim rng As Range
Dim dws As Worksheet
Dim dlr As Long
Application.ScreenUpdating = False
On Error GoTo Skip
Set tbl = ActiveSheet.ListObjects("Table1")
If Not Intersect(Target, tbl.DataBodyRange.Columns(21)) Is Nothing Then
Application.EnableEvents = False
Set rng = Intersect(ActiveSheet.Rows(Target.Row), Columns("A:AJ"))
rng.Copy
Select Case Target.Value
Case "Yes"
Set dws = ThisWorkbook.Worksheets("Completed RCD Orders")
End Select
dlr = dws.Cells(Rows.Count, "D").End(xlUp).Row + 1
dws.Range("A" & dlr).PasteSpecial xlPasteValues
rng.Delete
End If
Skip:
Application.CutCopyMode = 0
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Dec 05 2022 06:42 AM
Dec 05 2022 08:40 PM