Hi,
I'm getting a getting a runtime error 1004 on this line tbl2.ListRows(x).Range.PasteSpecial xlPasteAll (full code below). I've tried a few variations on this and can't seem to get the copied table row to paste into my second table. The range size is exactly the same and they are one two different worksheets. Can anyone point out where I've gone wrong?
Many Thanks
I'm getting a getting a runtime error 1004 on this line tbl2.ListRows(x).Range.PasteSpecial xlPasteAll (full code below). I've tried a few variations on this and can't seem to get the copied table row to paste into my second table. The range size is exactly the same and they are one two different worksheets. Can anyone point out where I've gone wrong?
Many Thanks
VBA Code:
Dim ProjectID As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim tbl1 As ListObject
Dim tbl2 As ListObject
Dim CopyRow As Range
Dim i As Long
Dim x As Long
'set up variables
Set ws1 = Projects
Set tbl1 = ws1.ListObjects("ProjectTbl")
Set ws2 = Closed
Set tbl2 = ws2.ListObjects("ClosedTbl")
ProjectID = UserForm2.ProjectIDCmb.Value
i = Application.WorksheetFunction.Match(ProjectID, tbl1.ListColumns(1).DataBodyRange, 0)
Set CopyRow = tbl1.ListRows(i).Range
CopyRow.Copy
Set newrow = tbl2.ListRows.Add
x = tbl2.ListRows.Count
tbl2.ListRows(x).Range.PasteSpecial xlPasteAll