How to set up dynamic table using vba

Hello ,
I just wrote the macro to update the sheet2 balance and date based on the data entered in sheet1.
My macro is working but I am a vba beginner so I am thinking if you can show me another way
to write it better. My problem is I do not know how set up dynamic tables so the data in sheet1 and sheet2 just continue to grow
Sheet2 has unique values for Name only

Thanks,

Title updated...

    Dim Table1 As Range, _
        Table2 As Range, _
        Table3 As Range, _
        Table4 As Range
    
    'Define the range for calculations
    With Sheet1
        Var_Row = .Cells(.Rows.Count, "E").End(xlUp).Row
        Set Table1 = .Range("E4:E" & Var_Row)
    End With  'sheet1
    
    With Sheet2
        Var_Row = .Cells(.Rows.Count, "C").End(xlUp).Row
        Set Table2 = .Range("B2:C" & Var_Row)
    End With  'sheet2
    
    With Sheet3
        Var_Row = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Table3 = .Range("A2:A" & Var_Row)
    End With  'sheet3
    
    With Sheet4
        Var_Row = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set Table4 = .Range("B2:B" & Var_Row)
    End With  'sheet4

Thank you very much, but you define table 3 and 4 for sheet 3 and 4 but I only has sheet1 and sheet2 repeated with different range. Also is there anothe way to re-write the script instead of hard coding the ranges?

Private Sub CommandButton1_Click()
    Dim Var_Row As Long
    Dim Var_Col As Long
    Dim objDate1 As Date, _
        objDate2 As Date
    Dim Table1      As Range, _
        Table2      As Range, _
        Table3      As Range, _
        Table4      As Range, _
        FoundCell   As Variant
    
    'find the column with header "Name" and use its row and column
    'values to specify the tables dynamically
    
    With Sheet1
        Set FoundCell = .Cells.Find("Name").Offset(1, 0)
        Var_Row = .Cells(.Rows.Count, FoundCell.Column).End(xlUp).Row - FoundCell.Row + 1

        Set Table1 = FoundCell.Resize(Var_Row)
    End With  'sheet1
    
    With Sheet2
        Set FoundCell = .Cells.Find("Name").Offset(1, 0)
        Var_Row = .Cells(.Rows.Count, FoundCell.Column).End(xlUp).Row - FoundCell.Row + 1
        
        Set Table2 = FoundCell.Resize(Var_Row, 2)
        Set Table3 = FoundCell.Offset(0, -1).Resize(Var_Row)
        Set Table4 = FoundCell.Resize(Var_Row)
    End With  'sheet2
    
    'First cell in the range
    
    Var_Row = Sheet1.Range("F4").Row
    Var_Col = Sheet1.Range("F4").Column
    
    'Copy balance from sheet2 to sheet1 for calculation
    
    For Each C1 In Table1
        Sheet1.Cells(Var_Row, Var_Col) = Application.WorksheetFunction.VLookup(C1, Table2, 2, False)
        objDate1 = CDate(Application.WorksheetFunction.Index(Table3, Application.WorksheetFunction.Match(C1, Table4, 0), 1))
        objDate2 = CDate(Sheet1.Cells(Var_Row, Var_Col - 2).Value)
    
    'Comparing date before update the sheet2 Date and Balance after calculation
    
        'MsgBox ("value2 is " & objDate2)
        If objDate2 > objDate1 Then
            With Sheet1
                .Cells(Var_Row, Var_Col + 2).Value = .Cells(Var_Row, Var_Col).Value _
                    - .Cells(Var_Row, Var_Col + 1).Value _
                    + Sheet1.Cells(Var_Row, Var_Col + 3).Value
            End With 'sheet1
            With Sheet2
                .Cells(Application.WorksheetFunction.Match(C1, Table4, 0) + 1, 3).Value = Sheet1.Cells(Var_Row, 8).Value
                .Cells(Application.WorksheetFunction.Match(C1, Table4, 0) + 1, 1).Value = Sheet1.Cells(Var_Row, 4).Value
            End With    'sheet2
        End If
       Var_Row = Var_Row + 1
    Next C1
    MsgBox "Done"
End Sub

Thank you very much for your enthusiasm. Is there any one show me the steps to close the question with [Solved] tag again please.