Click here to download the workbook which contains the below code

Excel VBA Code to Get All Sheets Names
Sheet Names Output

Copy below code and paste it then click F5 to execute the code.

Sub sheet_names()
'This Code will Get the Sheet Names in Current Workbook
'All Sheet Names are Listed in the Sheet1 in A Column

Dim ThsWB As Workbook
Dim ThWb_Sh1 As Worksheet
Dim sht_cntr, Sht_num, Last_Rw As Long

Set ThsWB = ThisWorkbook
Set ThWb_Sh1 = ThsWB.Sheets("Sheet1")

sht_cntr = ThsWB.Sheets.Count

For Sht_num = 1 To sht_cntr
Last_Rw = ThWb_Sh1.Cells(Rows.Count, 1).End(xlUp).Row
ThWb_Sh1.Range("A" & Last_Rw + 1) = ThsWB.Sheets(Sht_num).Name
Next Sht_num

End Sub
Explanation of the VBA code

Below 3 Lines are Variable Declarations.

Line1: Dim ThsWB As Workbook
Line2: Dim ThWb_Sh1 As Worksheet
Line3: Dim sht_cntr, Sht_num, Last_Rw As Long

In Lines 4 and 5 we are assigning the values to already declared variables “ThsWB ” & “ThWb_Sh1 “. As Workbook and Worksheet are Objects we need to assign values to them.

Line4: Set ThsWB = ThisWorkbook
Line5: Set ThWb_Sh1 = ThsWB.Sheets(“Sheet1”)

Line5: sht_cntr = ThsWB.Sheets.Count
In Line5 code we are finding the total number of sheets in the workbook.

Line6: For Sht_num = 1 To sht_cntr
Line7: Last_Rw = ThWb_Sh1.Cells(Rows.Count, 1).End(xlUp).Row
Line8: ThWb_Sh1.Range(“A” & Last_Rw + 1) = ThsWB.Sheets(Sht_num).Name
Line9: Next Sht_num

In-Line6 we are starting Loop to get filenames in the workbook. In Loop to hold the sheet number we have created the Sht_num variable. To end the loop once all the sheets are completed. So we have given the end value to stop the loop is variable sht_cntr.

In-Line7 we are calculating the Last Row in the “A” Column to update the Next Blank Cell with Sheet Name

In-Line8 in This COde we are updating the Sheet Names in A Column.

In-Line9 In this code we can move to Next Sheet