Formulas corresponding to the correct table in my sheet, vba. Multiple tables across multiple sheets

Question

I am setting up a cycle count sheet using VBA. I am having trouble with the formulas in my tables only referring to the first table in the sheet for the input values.

For example, I have 4 tables per sheet, the headers table 1 starting in cell A2. C3=B3*A3. Table 2, the formula is C68=B3*A3. Where I need it to read C68=B68*A68.

I just started learning VBA, so I am sure I am missing something. This is just part of the macro. I have my sheets set up as each month. Any help would be appreciated.

Dim i As Integer
Dim monthName As String
Dim WEEKOF As Integer
Dim DATEVALUE As Date
Dim SPACE As Integer
Dim ws As Worksheet
Dim cell As Range
Dim tableName As String
Dim tableRange As Range
Dim tbl As ListObject
WEEKOF = 1
SPACE = 0
Worksheets(WEEKOF).Activate
Range("A1").Formula = "=DATE(YEAR(TODAY()),MONTH(1),WEEKDAY(3,1))"
DATEVALUE = Range("A1")
For WEEKOF = 1 To 12
    Do Until Month(DATEVALUE) > WEEKOF
        Worksheets(WEEKOF).Activate
        Range("A1").Offset(SPACE, 0).Value = DATEVALUE
        'Adding forumlas and setting up total boxes
        Range("I3:I50").Offset(SPACE, 0).Formula = "=H3*G3"
        Range("L3:L50").Offset(SPACE, 0).Formula = "=IF(ABS(O3)>=10,""NEEDS RECOUNT"",""N/A"")"
        Range("N3:N50").Offset(SPACE, 0).Formula = "=E3-D3"
        Range("O3:O50").Offset(SPACE, 0).Formula = "=F3-D3"
        Range("P3:P50").Offset(SPACE, 0).Formula = "=F3/D3"
        Range("R3:R50").Offset(SPACE, 0).Formula = "=ABS(I3)"
        Range("S3:S50").Offset(SPACE, 0).Formula = "=ABS(O3)"
        Range("A52").Offset(SPACE, 0).Value = "Totals"
        Range("A52").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A53").Offset(SPACE, 0).Value = "Adjustment Cost Total"
        Range("A53").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A54").Offset(SPACE, 0).Formula = "=SUM(I3:I50)"
        Range("A55").Offset(SPACE, 0).Value = "Gross Discrepancy Cost"
        Range("A55").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A56").Offset(SPACE, 0).Formula = "=SUM(R3:R50)"
        Range("A57").Offset(SPACE, 0).Value = "Total on Hand Counted Parts"
        Range("A57").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A58").Offset(SPACE, 0).Formula = "=SUM(F3:F50)"
        Range("A59").Offset(SPACE, 0).Value = "Gross Discrepancy Count"
        Range("A59").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A60").Offset(SPACE, 0).Formula = "=SUM(S3:S50)"
        Range("A61").Offset(SPACE, 0).Value = "Number of Lines"
        Range("A61").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A62").Offset(SPACE, 0).Formula = "=COUNTA(UNIQUE(FILTER(A3:A50,A3:A50<>"""")))"
        SPACE = SPACE + 65
        DATEVALUE = DateAdd("D", 7, DATEVALUE)
        If Year(DATEVALUE) > Year(Date) Then
        Exit Do
        End If
    Loop
SPACE = 0
If Year(DATEVALUE) > Year(Date) Then
Exit For
End If
Next WEEKOF

Answer 1

A good idea is to change your formula definition style to FormulaR1C1 as below:

Sub Test()
   Dim i As Integer
   Dim monthName As String
   Dim WEEKOF As Integer
   Dim DATEVALUE As Date
   Dim SPACE As Integer
   Dim ws As Worksheet
   Dim cell As Range
   Dim tableName As String
   Dim tableRange As Range
   Dim tbl As ListObject
   WEEKOF = 1
   SPACE = 0
   Worksheets(WEEKOF).Activate
   Range("A1").Formula = "=DATE(YEAR(TODAY()),MONTH(1),WEEKDAY(3,1))"
   DATEVALUE = Range("A1")
   For WEEKOF = 1 To 12
      Do Until Month(DATEVALUE) > WEEKOF
         Worksheets(WEEKOF).Activate
         Range("A1").Offset(SPACE, 0).Value = DATEVALUE
        'Adding forumlas and setting up total boxes
         Range("I3:I50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-1]*RC[-2]"
         Range("L3:L50").Offset(SPACE, 0).FormulaR1C1 = "=IF(ABS(RC[3])>=10,""NEEDS RECOUNT"",""N/A"")"
         Range("N3:N50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-9]-RC[-10]"
         Range("O3:O50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-9]-RC[-11]"
         Range("P3:P50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-10]/RC[-12]"
         Range("R3:R50").Offset(SPACE, 0).FormulaR1C1 = "=ABS(RC[-9])"
         Range("S3:S50").Offset(SPACE, 0).FormulaR1C1 = "=ABS(RC[-4])"
         Range("A52").Offset(SPACE, 0).Value = "Totals"
         Range("A52").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A53").Offset(SPACE, 0).Value = "Adjustment Cost Total"
         Range("A53").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A54").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-51]C[8]:R[-4]C[8])"
         Range("A55").Offset(SPACE, 0).Value = "Gross Discrepancy Cost"
         Range("A55").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A56").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-53]C[17]:R[-6]C[17])"
         Range("A57").Offset(SPACE, 0).Value = "Total on Hand Counted Parts"
         Range("A57").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A58").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-55]C[5]:R[-8]C[5])"
         Range("A59").Offset(SPACE, 0).Value = "Gross Discrepancy Count"
         Range("A59").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A60").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-57]C[18]:R[-10]C[18])"
         Range("A61").Offset(SPACE, 0).Value = "Number of Lines"
         Range("A61").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A62").Offset(SPACE, 0).FormulaR1C1 = "=COUNTA(UNIQUE(FILTER(R[-59]C:R[-12]C,R[-59]C:R[-12]C<>"""")))"
         SPACE = SPACE + 65
         DATEVALUE = DateAdd("D", 7, DATEVALUE)
         If Year(DATEVALUE) > Year(Date) Then Exit Do
      Loop
      SPACE = 0
      If Year(DATEVALUE) > Year(Date) Then Exit For
   Next WEEKOF
End Sub