Forum Discussion
tkdkenney
Dec 05, 2022Copper Contributor
VBA Macro - Add table row and paste
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 bot...
tkdkenney
Dec 05, 2022Copper Contributor
Update: I added a table and got it to paste in a table, just need it to add a row and paste in that row.
Here is updated 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
Dim tblCO As ListObject
Application.ScreenUpdating = False
On Error GoTo Skip
Set tbl = ActiveSheet.ListObjects("Table1")
Set tblCO = Sheet3.ListObjects("CompletedOrders")
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
dws.Range(tblCO).PasteSpecial xlPasteValues
rng.Delete
End If
Skip:
Application.CutCopyMode = 0
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Here is updated 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
Dim tblCO As ListObject
Application.ScreenUpdating = False
On Error GoTo Skip
Set tbl = ActiveSheet.ListObjects("Table1")
Set tblCO = Sheet3.ListObjects("CompletedOrders")
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
dws.Range(tblCO).PasteSpecial xlPasteValues
rng.Delete
End If
Skip:
Application.CutCopyMode = 0
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
- peiyezhuDec 06, 2022Bronze ContributorTo only one row copy to the bottom of another sheet,is't it?
Set rng = Intersect(ActiveSheet.Rows(Target.Row), Columns("A:AJ"))
?
copy current entire row?