Create a new sheet for the compilation. Leave the sheet name as Sheet1 (or if you choose to rename it, note to change the reference in the below VBA code).

Go to the Visual Basic Editor and insert a new module (see Hack 85 on how to do this)

Place the VBA code below into the new module:

Sub ExtractWorksheetNames()

i = 1

For Each s In ActiveWorkbook.Worksheets
Sheets("Sheet1").Cells(i, 1).Value = s.Name
i = i + 1
Next s

End Sub

Go back to the worksheet, and run the code by selecting Developer > Macros (or keyboard shortcut Alt+F8), selecting the name of the macro (“ExtractWorksheetNames”) and clicking the “Run” button.

The sheet names will be compiled onto Column A of the new worksheet.