I have a workbook with a menu and 122 sheets holding detail for unique item (product model).

On the menu there is an index of the sheet names which was hand typed. However, the sheet names change as models evolve/get replaced.

I'm no VBA expert but i've written a small macro to put the sheet name in cell J1 of each sheet.


Sub set_sheetname()

Application.ScreenUpdating = False

Dim sh As Worksheet

For Each sh In Worksheets

sh.Activate

Range("i1").Select
Selection = "Sheet Name:"
Range("j1").Formula = "=MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,256)"
Columns("I:K").EntireColumn.AutoFit

Application.ScreenUpdating = True

Next

End Sub
On my menu page i was hoping to do something like this:

A B
Index NAME
2 =sheet[A2].$J$1
3 =sheet[A3].$J$1

That way i could just drag this down and my index list would complete itself and would dynamically update as sheet names change....