我正在尝试将行添加到一个工作表上的表中,其中包含来自不同工作表的数据。下面的代码在一定程度上起作用。
我可以让它一次添加一行数据,并确定将数据添加到表的位置。但是,我希望它能够添加多行数据,同时仍然能够确定表中将添加哪些数据。
我尝试过不同的方法来实现这个过程,然而,它们似乎都有问题。我可以插入多个行,但不能确定它们在表中的位置,或者无法一次添加多个行。
Sub AddData()
Dim ws As Worksheet
Dim tbl As ListObject
Dim NewRow As ListRow
Set ws = ActiveWorkbook.Worksheets("DATA Member-19")
Set tbl = ws.ListObjects("MemberInfo19")
Set NewRow = tbl.ListRows.Add
With NewRow
.Range(1) = Sheets("Add Members").Range("B4")
End With
End Sub新行的范围将从B4开始,并将根据需要添加多少数据而改变。它可以是一行,但也可以是需要传输的几行数据。
发布于 2022-06-24 14:26:39
我假设您实际上正在处理两个表(?)如果数据与搜索条件或成员号输入相匹配,希望将数据从Table1移动/复制到Table2?尝试以下代码:
Sub MoveMemberData()
Dim SearchCell As Range
Dim T1row As Long 'Row count Table1
Dim T2row As Long 'Row count Table2
Dim SearchRow As Long 'Searchrow count
Dim DataRow As Long 'Use later to delete records on Table 1 if required
Dim Tbl1 As ListObject, Tbl2 As ListObject
Set Tbl1 = MySheet1.ListObjects("MyTable1")
Set Tbl2 = MySheet2.ListObjects("MyTable2")
T1row = Worksheets("MySheet1").UsedRange.Rows.Count
T2row = Worksheets("MySheet2").UsedRange.Rows.Count
If T2row = 0 Then
If Application.WorksheetFunction.CountA(Worksheets("MySheet2").UsedRange) = 0 Then T2row = 0
End If
Set SearchCell = Worksheets("MySheet1").Range("B4:B" & T1row)
On Error Resume Next
Application.ScreenUpdating = False
For SearchRow = 1 To SearchCell.Count
If CStr(SearchCell(SearchRow).Value) = "MemberInfo19" Then
T2row = T2row + 1
Tbl2.ListRows.Add.Range.Value = Tbl1.ListRows(SearchRow).Range.Value
End If
Next
' Add this next loop to go through Tbl1 and delete the rows you copied (if its required)
For DataRow = 1 To SearchCell.Count
If CStr(SearchCell(DataRow).Value) = "MemberInfo19" Then
Tbl1.ListRows(DataRow).Delete
DataRow = DataRow - 1
End If
Next
Application.ScreenUpdating = True
End Subhttps://stackoverflow.com/questions/70449807
复制相似问题