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

Jaycal · Accepted Answer · 2013-11-20 14:06:25Z

3

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

answered Nov 20, 2013 at 14:06

Jaycal

2,0871 gold badge13 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AMPorter Over a year ago

This is fantastic, thank you. I know it could be done some how, just did not have your expertise. Your help and prompt respose is most appreciated.

AMPorter Over a year ago

i have now used the solution with some production data instead of A1, B1, etc, but the solution now fails! Instead of writing batch of records to the new worksheet over writes the existing information. Is there any characters within the data i should be concerned about? The characters within the production data is just alpha-numeric, so not sure what the issue is. Any assistance that you can provide would be most appreciated as i have not got a clue!!

Jaycal Over a year ago

This code has a few assumptions. One is that the data is continuous across the columns. For example if row one is "A (blank) B" and row 2 is " A B C" the the result in row one would be "A A B C" because the continuous data in row one stopped with "A"

AMPorter Over a year ago

thank you for your response. I have done a little further investigation and there does noe seem to be an issue with the data, as i have tested this by adding one row at a time, then processing, which is does successfully until it reaches the 19th record! Once 19 records are used as a input, then only the last collection of records are displayed within the new worksheet. I have changed the contents of the 19th record to be the same as other ones successfully processed but the error keeps appearing, any ideas?

AMPorter Over a year ago

I have resolved the issue. I have added .DataBodyRange.Rows(i) within the else statement and it works fine. Thank yuo for all your assistance with this matter

Lucius Matos · Accepted Answer · 2019-01-18 19:06:48Z

0

Remember that "DataBodyRange" returns a Range. So do this:

'copies entire line 1 from "loInput" to "loOutput" on line 2
loOutput.DataBodyRange.Rows(2) = loInput.DataBodyRange.Rows(1)

In addition, just make a FOR to process the rest

answered Jan 18, 2019 at 19:06

Lucius Matos

1612 silver badges5 bronze badges

Collectives™ on Stack Overflow

Copying rows from one listobject to another using Excel VBA

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related