Cole o código abaixo na classe da sua pasta de trabalho:
Private Sub Workbook_Open()
Dim wsIndex As Worksheet
Dim iWorksheet As Worksheet
Set wsIndex = ThisWorkbook.Worksheets("Índice")
wsIndex.Cells.Delete
Application.Goto wsIndex.Range("A1")
For Each iWorksheet In ThisWorkbook.Worksheets
If iWorksheet Is wsIndex Then GoTo Continue
ActiveCell = iWorksheet.Name
ActiveCell.Offset(1).Select
Continue:
Next iWorksheet
wsIndex.Columns("A").Sort Key1:=wsIndex.Range("A1"), Order1:=xlAscending, Header:=xlNo
Application.Goto wsIndex.Range("A1")
End Sub
http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG