Formula Referencing Worksheet Name by Variable
 

Concatenation should work:

Set OldSht = ActiveSheet
Sheets.Add
NewShtName = ActiveSheet.Name
OldSht.Activate
Cells(1, 1).Formula = "='" & NewShtName & "'!A2"

"dafio" wrote:

I am setting up a macro that will add a new worksheet to a workbook. The macro will then need to enter a formula in a cell on an existing worksheet that references a cell on the new worksheet. I am assuming I can use a variable for the new worksheet name and concatenate it within the formula but it doesn't work.

e.g. - If the new worksheet is named "Smith", I would need to use VBA to enter the following formula on an existing worksheet:

=Smith!A2