Once you open the sheet and enable macros, you can use function:
=WorksheetsNo()
To get total number of sheets in that workbook
And use function:
=WorksheetName(1)
To get sheet name for first sheet, and
=WorksheetName(2)
To get sheet name for second sheet and so on, I used the function Row()-5 in cell B6 to get the 1 and in cell B7 to get the 2
=WorksheetName(ROW()-5)
