VBA-Excel: Get the names of all WorkSheets in a Excel (WorkBook)

• 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:

• Create worksheets with Names in Specific Format/Pattern.
• Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet
• Create or Add Worksheets at the Run time.

Also Read:

1. VBA-Excel: Create Dynamic or Run-time WorkSheets using Macro.
2. VBA-Excel: Copy/Paste data - Copy the Entire data from one sheet to another
3. VBA Excel - Cells, Ranges and Offset: Refer Range by using A1 Notations
4. VBA-Excel: Copy/Paste data - Copy the data from a call and paste it to another cell
5. Excel-VBA : Prevent Adding New Worksheet