How to define a variable the equals the name of the last sheet (VBA)?

Question

I have an Excel-file which contains a least two sheets: a summary sheet (Sheet1), a template sheet (Sheet2) and a variable amount of sheets. I have a small macro which copies Sheet2 to the very end of the sheets and renames the new sheet to whatever I type into an input box.

I also want the macro to make A1 in Sheet1 as a reference to whatever the value is in A1 of the new sheet ("Last Sheet"). But my problem is to define a variable that equals the name of the new sheet.

I've tried to use Sheets(Sheets.Count) with no luck.

Sheets("Sheet1").Range("A1").Formula = "='Sheets(Sheets.Count)'!A1"

I expect the formula ='NameOfTheLastSheet'!A1 to be present in A1 in Sheet1. Instead Excel wants me to open a file to update the value of Sheet(Sheets.Count).

(I'm a noob to VBA but I don't mind looking into certain areas, if I'm pointed in the right direction.)

Answer 1

You are close, just some issues with syntax.

Sheets("Sheet1").Range("A1").Formula = "='"& Sheets(Sheets.Count).Name & "'!A1"

Because Sheets(Sheets.Count).Name is vba syntax to return the name of the worksheet, it needs to be concatenated outside the rest of the actual formula string.