Copying rows from one listobject to another using Excel VBA

Question

I want to create a peice of VBA code which will select the first 10 rows from a ListObject and then paste them into another ListObject. Once a collection of rows has been copied to the new sheet, they will be processed, then the next 10 rows will be selected and processed, and so on.

I can achieve this using standard VBA functions, but I would like to achieve this using the ListObject, i.e.

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

Any help on this matter would be most appreciated as I would like to use ListObjects

Thank you in advance

Answer 1

DataBodyRange refers to the actual range of rows and columns in the table. If there are no rows in the table (which is your condition after executing DataBodyRange.Delete, you'll need to add a row to the table first; then, you can copy and paste data into that row, with Excel expanding the table accordingly

Your for loop can be updated to read as follows:

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