Getting a Formula in cells using vba.

[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")
    
    
    
    
    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
End Sub

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


End Sub

Okay so Now I need to know how to get a formula into a cell on the trial balance page. So the new sheet name is pasted on the trial balance and then in cell tB.cells(crow,4) I need to have a formula that references the sheet i just created. I can create multiple sheets so I need it to reference each sheet that is added to the trial balance. I have been researching for days. I am really confused.

does that make sense?

 

[StephenCrump]

Welcome to the Forum!

If I'm interpreting correctly, I think your question distils down to: how can I put a formula in one worksheet that references another worksheet?

If so, perhaps along these lines:

Sub Test()

    Dim aT As Worksheet, tB As Worksheet
    Dim NewName As String
    Dim cRow As Long
       
    Set aT = Sheets("Account Template")
    Set tB = Sheets("Trial Balance")
    NewName = "Some New Name"
    cRow = 10   'say
   
    aT.Copy before:=tB
    ActiveSheet.Name = NewName

    tB.Range("D" & cRow).Formula = "='" & NewName & "'!A1 * 2"  'say

End Sub