Need help with this Formula

[Sandy3503]

Sub addAccount()
    Dim aT As Worksheet
    Set aT = Sheets("Account Template")
    Dim tB As Worksheet
    Set tB = Sheets("Trial Balance")
    Dim cRow As Integer
    Dim lRow As Integer
    Dim dS As Worksheet
    Set dS = Sheets("Data Sheet")
    Dim SheetName As String

    
    aT.Copy before:=tB
    On Error Resume Next
    ActiveSheet.Name = frmaddAccount.ltbAcct.Value
    On Error GoTo 0
    
    ActiveSheet.Cells(2, 2).Value = frmaddAccount.ltbAcct.Value
    
    cRow = 4
    Do Until tB.Cells(cRow, 2).Value = Empty
        cRow = cRow + 1
    Loop
    
    lRow = 2
    Do Until dS.Cells(lRow, 5).Value = Empty
        lRow = lRow + 1
    Loop
    
    
    tB.Cells(cRow, 2).Value = frmaddAccount.ltbAcct.Value
    dS.Cells(lRow, 5).Value = frmaddAccount.ltbAcct.Value
    
    ActiveSheet.Name = SheetName.Value
    
    tB.Cells(cRow, 4).Formula = "If(-SUM('SheetName'!E4:E1000)+SUM('SheetName'!D4:D1000)<=0,,-SUM('SheetName'!E4:E1000)+SUM('sheetname'!D4:D1000))"
    
End Sub

I need the Formula in the last line of code to work. I can copy a sheet and add the sheet but now i need it so that when the sheet is copied and added to the trial balance that this formula appears in the cells to calculate the balance. Any idea of how that might work? I am new to coding and just really confused with this.

 

[maabadi]

Try This for formula but change all Sheet1 at formula to SheetName:

tB.Cells(cRow, 4).FormulaR1C1 ="=IF(SUM(Sheet1!R4C4:R1000C4)-SUM(Sheet1!R4C5:R1000C5)<=0,"""",SUM(Sheet1!R4C4:R1000C4)-SUM(Sheet1!R4C5:R1000C5))"

OR

tB.Cells(cRow, 4).FormulaR1C1 ="=IF(SUM(" & Sheetname & "!R4C4:R1000C4)-SUM(" & Sheetname & "!R4C5:R1000C5)<=0,"""",SUM(" & Sheetname & "!R4C4:R1000C4)-SUM(" & Sheetname & "!R4C5:R1000C5))"