Copy formula into a cell using vba

[BobtBuilder]

Hi folks I have this application that inserts a new sheet, based on a template, renames it and updates the table with the new name
Problem 1 After the cell.value = ... it inserts that value into the cell
Problem 2 I can not seem to figure out why the formula is giving me errors, even if I have done it successfully in the past with another formula. I have left some examples of what I have tried
Problem 3 it does not seem to exit the sub, I have to reset manually

Here is the code

Sub NewComp_Click()

Dim wsname As String
Dim cell As Range
Dim nextLocation As Integer
Dim BName As String
Dim AccType As String
Dim FormulaString As String
Dim sourceSheet As Worksheet
Dim newSheet As Worksheet
Dim prev As Range
Dim DestinationRow As ListRow
Dim DestinationTable As ListObject


' values from form
BName = Me.TextBox1.Value
AccType = Me.ComboBox1.Value

' Set the range of positions of worksheets
Set cell = Worksheets("Settings").Range("H3")
nextLocation = 0
' Find the next available location
Do While cell <> " "

nextLocation = cell.Value + 1
Set cell = cell.Offset(1, 0)
Loop
If nextLocation > cell Then

Else
cell.Value = Range("I" & nextLocation - 1)
End If

' Create new sheet
Sheets("Template").Copy After:=Sheets(cell.Value)
ActiveSheet.Name = BName

' setup to insert cells
wsname = "Settings"
Set ws = ThisWorkbook.Sheets("Settings")
Set DestinationTable = ws.ListObjects(wsname)
Set DestinationRow = DestinationTable.ListRows.Add

' formula to insert
FormulaString = "=TAKE(FILTER(INDIRECT(""'"" & I11 & ""'!K2:K"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!K:K"")) + 1)), (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <= TODAY()) * (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <> "")),-1)"

DestinationRow.Range(1, 1).Value = nextLocation
DestinationRow.Range(1, 2).Value = BName
' DestinationRow.Range(1, 3).Value = "=TAKE(FILTER(INDIRECT(""'"" & I11 & ""'!K2:K"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!K:K"")) + 1)), (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <= TODAY()) * (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <> "")),-1)"
' Range("J" & nextLocation).Formula = FormulaString

End Sub


Thank you

 

[BobtBuilder]

So problem 3 was I have never used forms before and forgot to use Unload Me

 

[BobtBuilder]

so after changing some code It runs but the copy is blank and it reformats the cell fillin.
I have tried
1) 'Range("J" & nextLocation).Formula = Range("J" & prev).Formula
2) Cells(prev, 10).Copy
Cells(nextLocation, 10).PasteSpecial xlPasteValues
3) DestinationRow.Range(1, 3).Formula = "=TAKE(FILTER(INDIRECT(" & _
"""'"" & I11 & ""'!K2:K"" & " & _
"(MATCH(9.99999999999999E+307, INDIRECT(" & _
"""'"" & I11 & ""'!K:K"")) + 1)), " & _
"(INDIRECT(" & _
"""'"" & I11 & ""'!A2:A"" & " & _
"(MATCH(9.99999999999999E+307, INDIRECT(" & _
"""'"" & I11 & ""'!A:A"")) + 1)) <= TODAY()) * " & _
"(INDIRECT(" & _
"""'"" & I11 & ""'!A2:A"" & " & _
"(MATCH(9.99999999999999E+307, INDIRECT(" & _
"""'"" & I11 & ""'!A:A"")) + 1)) <> """")),-1)"
but always the same issue

Can anyone help?

Thanks