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

[deadlyduck]

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

```
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.

[Richard Buttrey]

Try

Please Login or Register  to view this content.

[deadlyduck]

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