Add multiple rows of data to table-CodePudding

I am trying to write a macro in Excel to help me move raw banking data into a table contained on a separate worksheet.

Worksheets in my file:

Raw Data Sheet - This worksheet has raw banking data
Transactions Sheet - This worksheet contains a table ("Table1") where all banking transactions are stored

I would like to add all transactions listed in the "RawData" sheet to the bottom of Table1. The number of transactions varies each time I execute the macro, so I need to find a way to have the macro calculate the total number of rows required to be added to Table1 each time, and add all of the rows of data. So far the macro I have written correctly adds data to Table1, but only the first line of data. Additionally, I would like to have the data automatically delete from the "RawData" worksheet once it has been moved.

The code I am using currently is:

Dim ws As Worksheet
Dim newrow As ListRow
Dim inpt As Worksheet

Set ws = Worksheets("Transactions")
Set inpt = Worksheets("RawData")

Set newrow = ws.ListObjects("Table1").ListRows.Add

With newrow
.Range(1) = Range("A2")
.Range(2) = Range("B2")
.Range(3) = Range("C2")
.Range(4) = Range("D2")
.Range(5) = Range("E2")
.Range(6) = Range("F2")

End With

End Sub

I am using Office 365.

Much appreciation in advance for your help.

CodePudding user response：

You need to loop through the rows of the data. Something like:

i = 2

Do until Range("A" & i).Value = ""

    Set newrow = ws.ListObjects("Table1").ListRows.Add

    With newrow
        .Range(1) = Range("A" & i)
        .Range(2) = Range("B" & i)
        .Range(3) = Range("C" & i)
        .Range(4) = Range("D" & i)
        .Range(5) = Range("E" & i)
        .Range(6) = Range("F" & i)

    End With
    i = i   1
Loop