Table of Contents
Introduction/Problem Statement/Business Need:
In this article, we will show you how to List all sheets names in a Workbook using Excel VBA.
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).
' 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
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).
'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
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.