Hi dear all
I need to achieve the following string of actions
Filter specific value in a specific column in table A
Copy the filtered data range from table A (not the headers)
Go to a different sheet in the same workbook
Paste the copied data in the first row of table B, moving any existing rows of data down.
Actual Sheet and Table names
Sheet: PasteSiteData, Table: t_copypaste
Sheet: StakesArchive, Table: t_stakesarchive
I am trying to achieve this with the following code:
The result is almost achieved except that in table "t_stakesarchive" the above code adds as many extra rows as the actual count of rows in table "t_copypaste" but I want to add only as many rows as the filtered ones.
I can imagine that part of the issue probably exists in the following line:
Thank you in advance
I need to achieve the following string of actions
Filter specific value in a specific column in table A
Copy the filtered data range from table A (not the headers)
Go to a different sheet in the same workbook
Paste the copied data in the first row of table B, moving any existing rows of data down.
Actual Sheet and Table names
Sheet: PasteSiteData, Table: t_copypaste
Sheet: StakesArchive, Table: t_stakesarchive
I am trying to achieve this with the following code:
VBA Code:
Sub PasteSiteData_SendToArchive()
ActiveSheet.ListObjects("t_copypaste").Range.AutoFilter Field:=36, Criteria1 _
:="<>|"
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("t_copypaste")
Dim Rng As Range
Set Rng = tbl.DataBodyRange
Set Rng = Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1)
Dim r As Long
With Sheets("StakesArchive").ListObjects("t_stakesarchive")
For r = 2 To Rng.Rows.Count
.ListRows.Add (1)
Next r
With .DataBodyRange
Rng.Copy
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(Rng.Rows.Count + 1, .Columns.Count).Copy
.Cells(1, .Columns.Count).PasteSpecial Paste:=xlPasteFormulas
End With
End With
Application.CutCopyMode = False
End Sub
The result is almost achieved except that in table "t_stakesarchive" the above code adds as many extra rows as the actual count of rows in table "t_copypaste" but I want to add only as many rows as the filtered ones.
I can imagine that part of the issue probably exists in the following line:
VBA Code:
Set Rng = tbl.DataBodyRange
Thank you in advance