Copy dynamic rows into a table

Question

I would like to find a solution to this issue I am having.

I have a button that runs this code:

Sub CopyValues()

Dim ws As Worksheet

Set ws = Worksheets("report")

Dim oNewRow As ListRow

Set oNewRow = ThisWorkbook.Worksheets("report").Range("Table1").ListObject.ListRows.Add(AlwaysInsert:=True)

With ThisWorkbook.Sheets(1)

oNewRow.Range.Cells(1, 1).Value = ThisWorkbook.Worksheets(1).Range("A4")
oNewRow.Range.Cells(1, 2).Value = ThisWorkbook.Worksheets(1).Range("B4")
oNewRow.Range.Cells(1, 3).Value = ThisWorkbook.Worksheets(1).Range("D4")
oNewRow.Range.Cells(1, 4).Value = ThisWorkbook.Worksheets(1).Range("C4")
oNewRow.Range.Cells(1, 5).Value = ThisWorkbook.Worksheets(1).Range("G4")
oNewRow.Range.Cells(1, 6).Value = ThisWorkbook.Worksheets(1).Range("F4")
oNewRow.Range.Cells(1, 7).Value = ThisWorkbook.Worksheets(1).Range("E4")

End With

End Sub

When I press the button, it copies the values from the first row of a range to a table. Every time I press the button, it copies the same row to a new row in the table.

What I want is this: The range (it's a range not a table)is in the sheet called "source". When I press the button, I would like it to copy each row into a corresponding row on the table in sheet "report".

My guess is, the code above would have to be modified to include a loop in order to go to down each row until if finds a blank row, but then also the ranges would have to change from A4 to A5, and A6, and so on until blank. I don't know how to do that code in VBA.

NOTE: If you notice, in the code, the order is ABDCGFE, that is because the table in which I need to copy the values to is in different order than the source table. I NEED IT THAT WAY, that's why I can't do a simple copy paste.

NOTE 2: The rows from range in sheet "source" will eventually be dynamic!

Thank you very much

Are you looking for *any* blank rows, or the first row after the last row in the table? (it,s not quite the same thing. Perhaps [finding the last row](https://stackoverflow.com/questions/38882321/excel-vba-better-way-to-find-last-row) is what you need? — cybernetic.nomad, Sep 12 '18 at 17:36
No. Always the first blank row after the last row that I need. let's say that the range goes from A4 to A10. The first blank row would be A11. However, the number of rows will always vary. It could be A10 or A6 or A20. — colonel179, Sep 12 '18 at 17:42
But would there ever be something after A11? (Say at A12 or A333) — cybernetic.nomad, Sep 12 '18 at 17:44
No there will not be anything. It will always be blank since after the rows that are filled every other cells will be blank. Just think like a table that is just expanding, but there is nothing else in the sheet. Is there a way to attacha file here? I could attached a file with an example. — colonel179, Sep 12 '18 at 17:53
Then A10 is the last row and you can look for it easily enough (see the link provided in my first comment) — cybernetic.nomad, Sep 12 '18 at 18:33
But then how would I change the values from the first one to the second one? The code above only copies the first column, because it has fixed values (A4, B4, C4, etc). How do I tell excel to go to the second row and change it to A5, B5, C5, etc)? — colonel179, Sep 12 '18 at 19:48

Tim Williams · Answer 1 · 2018-09-12T21:59:35.110

0

Something like this (untested) should work:

Sub CopyValues()

    Dim rwSource As Range, lo As ListObject

    Set rwSource = Worksheets("source").Rows(2) '<< first row to copy from

    Set lo = ThisWorkbook.Worksheets("report").Range("Table1").ListObject

    Do While Application.CountA(rwSource) > 0

        'Assign the values in one shot as an array....
        lo.ListRows.Add(AlwaysInsert:=True).Range.Cells(1).Resize(1, 7).Value = _
               Array(rwSource.Cells(1), rwSource.Cells(2), rwSource.Cells(4), _
                     rwSource.Cells(3), rwSource.Cells(7), rwSource.Cells(6), _ 
                     rwSource.Cells(5))

        Set rwSource = rwSource.Offset(1, 0) '<< next source row
    Loop

End Sub

edited Sep 12 '18 at 21:59

answered Sep 12 '18 at 21:49

Tim Williams

154,628
8
97
125

THANK YOU!!! This works perfect and is everything I needed!! BUT (I hate to have this but), once I adapted it to my file there is an issue. The source table is filled with formulas because it is an INDEX, so the code takes them into consideration so it copies every single row that has a formula (I stopped at 300). Is it possible to make it ignore the formulas? When doing it in a blank cell as norma, it works perfectly, though! Thank you very much! – colonel179 Sep 12 '18 at 23:36
E.g. `Do While Len(rwSource.Cells(1)) > 0` would keep going until it hit a row with nothing in ColA – Tim Williams Sep 13 '18 at 02:58

Copy dynamic rows into a table

1 Answers1