Formula to identify the active sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Formula to get all the active sheet name:
The below formula needs to be entered in Name Manager.
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
After that in sheet we have to insert the formula "=all_sheet_name"