Click here to download the workbook which contains the below code
Copy below code and paste it then click F5 to execute the code.
Sub sheet_names() 'www.comexcelhub.com '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