- Open a new Excel WorkBook and press “Alt+F11” to open the Visual Basic Editor
- Copy Paste the following code
Sub FnGetSheetsName() Dim mainworkBook As Workbook Set mainworkBook = ActiveWorkbook For i = 1 To mainworkBook.Sheets.count ‘Either we can put all names in an array , here we are printing all the names in Sheet 2 mainworkBook.Sheets(“Sheet2”).Range(“A” & i) = mainworkBook.Sheets(i).Name Next i End Sub
- Run the Macro
Explanation:
mainworkBook.Sheets.count
Sheets.count will give you the number of sheets present in the Excel (WorkBook)
mainworkBook.Sheets(i).Name
Sheets(i).Name will fetch the Sheet Name based upon the index value, which is ‘i’ here.
Also Read about: