Updating multiple table entries by date with VBA

[dashinaigokakai]

Hello,

New to the forum and so far impressed with the level of support provided to people. I am just starting my journey on VBA and need help with what I think it's an easy macros that I just can't figure out.

I need to populate "Table 2" with the past 10 years worth of daily data copied from "Table 1". Table 1 is just a 2x6 table (including headers) with parameters that change based on the date value assigned to a cell (see image 1).

The idea is to loop this cell day by day from Jan 1 2014 to today()-1 while copying the values that populate in table 1 into table 2 (see images). Table 1 doesn't have a column with a date so that will need to be added to Table 2 to properly match the data with its corresponding date.

Thank you in advance for the help.

 

[NoSparks]

you can give this a try on a copy of your workbook

Sub Testing_1()
    Dim srcTbl As ListObject, destTbl As ListObject, oNewrow As ListRow
    Dim i As Long, x As Long, y As Long
    Dim startDate As Date, endDate As Date
    Dim dteCell As Range, arr As Variant

Set srcTbl = Sheets("Raw_data").ListObjects("Table1")
Set destTbl = Sheets("Compiled_data").ListObjects("Table2")
Set dteCell = Sheets("Raw_data").Range("B1")

startDate = "1/1/2014"
    x = CLng(startDate)
endDate = Date - 1
    y = CLng(endDate)
Application.ScreenUpdating = False
For i = x To y
    dteCell = i
    ' maybe needing a delay for calculations? I don't know.
    arr = srcTbl.ListRows(1).Range.Cells(1).Resize(, 6).Value
    With destTbl
        Set oNewrow = .ListRows.Add(AlwaysInsert:=True)
        oNewrow.Range.Cells(1, 1) = i
        oNewrow.Range.Cells(1, 2).Resize(, 6) = arr
    End With
Next i
Application.ScreenUpdating = True
End Sub