excel - 使用 Excel VBA 将行从一个列表对象复制到另一个

Question

我想创建一段 VBA 代码，它将从 ListObject 中选择前 10 行，然后将它们粘贴到另一个 ListObject 中。将一组行复制到新工作表后，将对其进行处理，然后选择并处理接下来的 10 行，依此类推。

我可以使用标准 VBA 函数来实现这一点，但我想使用 ListObject 来实现这一点，即

Sub PopulateSectionOfData()

    Dim loInput As ListObject
    Dim loOutput As ListObject
    Dim intNumOfColumns As Integer
    Dim rngToCopy As Range
    Dim intRowsInFile As Integer

    ' Create the inout and output ListObjects
    Set loInput = Worksheets(WS_INPUT).ListObjects(LO_INPUT)
    Set loOutput = Worksheets(WS_OUTPUT).ListObjects(LO_OUTPUT)

    ' Delete all the current rows from the output table
    Worksheets(WS_OUTPUT).ListObjects(LO_OUTPUT).DataBodyRange.Delete

    ' Set variables
    intNumOfColumns = loInput.ListColumns.Count
    WorkbookCounter = 1
    intRowsInFile = 10                   ' Select 10 records at a time

    ' Get 10 records at a time from the input file
    For i = 1 To loInput.DataBodyRange.Rows.Count Step intRowsInFile - 1

        '???? - This is the area that i am unsure on
        Set rngToCopy = loInput.DataBodyRange(p, 1)
        rngToCopy.Copy loOutput

        '????

        ' TODO - Add further processing here
    Next i

    ' Clear used objects
    Set loInput = Nothing
    Set loOutput = Nothing

End Sub

非常感谢您对此事的任何帮助，因为我想使用 ListObjects

先感谢您

Answer 1

DataBodyRange指表中行列的实际范围。如果表格中没有行(这是您执行 DataBodyRange.Delete 后的情况，您需要先在表格中添加一行；然后，您可以将数据复制并粘贴到该行中，Excel 会相应地展开表格

您的 for循环可以更新为如下所示:

For i = 1 To loInput.DataBodyRange.Rows.Count Step intRowsInFile - 1

    ' First add a blank row to the table
    loOutput.ListRows.Add

    ' Check that 10 rows are available in the input table
    ' (Done to avoid adding blank rows to the output table)
    If i + 9 > loInput.DataBodyRange.Rows.Count Then
        loInput.DataBodyRange.Rows(i & ":" & loInput.DataBodyRange.Rows.Count) _
            .Copy loOutput.DataBodyRange.Rows(i)
    Else
        loInput.DataBodyRange.Rows(i & ":" & i + 9).Copy loOutput
    End If
Next i