List all sheets name in a Workbook using Excel VBA

List all sheets names
  • Save

Introduction/Problem Statement/Business Need:

In this article, we will show you how to List all sheets names in a Workbook using Excel VBA.

Solution:

Point 1.

This code will run a loop(For Each Next Loop) through each sheet and writes the sheet name of each sheet sequentially to a sheet that we choose (Sheet1 chosen in code).

List all Worksheets in a Workbook
  • Save

Sub KDataScience_ListAllworksheetName()
    ' Declare variable x of type integer
    Dim x As Integer
    'Assign x value to 2
    x = 2
    'Declare wks variable of type worksheet
    Dim wks As Worksheet
    
    'Go through each worksheet in this workbook
    For Each wks In ThisWorkbook.Worksheets
        'To get sheet name and paste them into sheet1
        Sheet1.Cells(x, 1).Value = wks.Name
        'Increment value of x by 1 for new sheet name paste into next blank cells in sheet1
        x = x + 1
    Next wks
End Sub

Point 2.

This code will also run a loop(For Next Loop) through each sheet and writes the sheet name of each sheet sequentially to a sheet that we choose (Sheet1 chosen in code).

list All worksheets name
  • Save

Sub KDataScience_ListAllworksheetName()
    
    'Declere a variable of type integer
    Dim i As Integer
    'Loop for each sheet
    For i = 1 To Sheets.Count
        Sheet1.Cells(i, 1).Value = Sheets(i).Name
        Next i
    
End Sub

Conclusion:

If you want to list all sheets names in a workbook then you may use any of the above code instead of manually doing it.

Don't miss out!
Subscribe To Newsletter

Get the latest Excel, VBA, Automation, Tips & Tricks, delivered straight to your inbox.

Invalid email address
Give it a try. You can unsubscribe at any time.

Leave a Reply

Your email address will not be published. Required fields are marked *

Copy link
Powered by Social Snap