Using VBA to add a formula to a range with a variable as part of the formula

I'm trying to create a cell formula using VBA with a variable but am running into errors. The objective of the formula is to identify the sheet name of the last created worksheet. Here's a working example of the formula:

=RIGHT(CELL("filename",Sheet2!A1),LEN(CELL("filename",Sheet2!A1))-FIND("]",CELL("filename",Sheet2!A1)))

This would identify that the last sheet created is called 'Sheet2'- if you enter it in a cell, the output is 'Sheet2'
The problem is that I obviously need to use a variable in place of where the above formula contains references to Sheet2!A1. I've tried a few variations to no avail, the most recent being the following:

Sub IdentifyNewSheetName()
'
    Dim ws As Worksheet
    Dim tgtCell As Range
    Set ws = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Set tgtCell = ws.Range("a1")
    ActiveCell.Formula = "=RIGHT(CELL(""filename""," & tgtCell & "),LEN(CELL(""filename""," & tgtCell & "))-FIND(""]"",CELL(""filename""," & tgtCell & ")))"

End Sub

```
I suspect that it might be something to do with all the inverted commas but am stumped! A couple of Google searches didn't quite fix it for me and a quick search here didn't seem to point the way out. Anyone see a fix? Many thanks to all who respond.

Try

ActiveCell.Formula = "=RIGHT(CELL(""filename""," & tgtCell.Value & "!A1),LEN(CELL(""filename""," & tgtCell.Value & "!A1))-FIND(""]"",CELL(""filename""," & tgtCell.Value & "!A1)))"

Thanks for this Richard- it's a neater solution than the one I came up with soon after I had requested help!

My solution was to create a string variable [MyString = "'" & ws.Name & "'!" & tgtCell.Address] and use this string variable in place of where your single step solution has tgtcell.value &"!A1

Am marking the post as SOLVED