Copy range with dynamic rows into a table in other sheet?

[colonel179]

Hello,

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 be dynamic!

Thank you very much