Get All Sheets Names In Workbook with VBA Excel for mac and windows

Video

This VBA code allows you to get this of all sheets name in current workbook by creating new worksheet.

You can use the code in Excel for mac & windows

1. Copy the below VBA Code by clicking on the copy box
2. Open Microsoft Visual Basic for Applications window Developer Ribbon Tab > Visual Basic (Shortcut keys as follows for mac & windows)
   1. mac users fn  + ⌥ [option] + F11 
   2. windows users Alt  + F11
3. From the menu bar select Insert > Module and insert new Module
4. Paste the code into the module

'Coded By Faraz Shaikh
'Visit : www.ExcelExciting.com
Sub GetSheetNames()
  
    Dim ws As Worksheet
    Dim i As Integer
   
    Sheets.Add
    ActiveSheet.Name = "SheetNames"
    ActiveSheet.Move Before:=ActiveWorkbook.Sheets(1)
    'Visit : www.ExcelExciting.com
  	i = 1 
    For Each ws In Worksheets
        ActiveSheet.Cells(i, 1) = ws.Name
        i = i + 1
    Next ws
    'Visit : www.ExcelExciting.com
End Sub

'Coded By Faraz Shaikh

'Visit : www.ExcelExciting.com

Sub GetSheetNames()

    Dim ws As Worksheet

    Dim i As Integer

    Sheets.Add

    ActiveSheet.Name = "SheetNames"

    ActiveSheet.Move Before:=ActiveWorkbook.Sheets(1)

    'Visit : www.ExcelExciting.com

    i = 1 

    For Each ws In Worksheets

        ActiveSheet.Cells(i, 1) = ws.Name

        i = i + 1

    Next ws

    'Visit : www.ExcelExciting.com

End Sub

Once the code is pasted into the module, close the VBA editor and goto

• Developer Tab > Macros
• List of all Macros will appear
• Select the macro “GetSheetNames”
• Click the run button to execute the code.

Was the information helpful in this blog?

• If yes, hit that share button and show the excitement to the world.
• Subscribe to our free posts.

• If no, please let us know what to improve.