How to get a list of sheets in an Excel file
Getting a list of the sheet names in an Excel file can make it more convenient to check and manage the sheets. If your file has only a few sheets, then there may not be many issues. However, when the number of sheets increases, keeping track of the data in the sheets becomes essential. Creating a table of contents for your Excel file is a great way to start, and getting a list of the sheet names is the first step. Follow the steps below to learn how to get a list of sheets in an Excel file.
1. How to get the name of the current sheet
To get the name of the current sheet, you only need to enter the following formula into the cell where you want the name of the sheet:
=RIGHT(CELL("filename");LEN(CELL("filename"))-FIND("]";CELL("filename")))
How to get a list of sheets in an Excel file
2. How to get a list of sheets in an Excel file
To get a list of the sheets in an Excel file, first select the Developer tab on the ribbon. Then select Visual Basic. Alternatively, you can use the keyboard shortcut Alt + F11 to open the VBA window.
The VBA window will appear.
How to get a list of sheets in an Excel file
Select Insert => Module.
How to get a list of sheets in an Excel file
Then copy the code below into the Module dialog box.
Private Sub CreateTableOfContents()
Dim wsSheet As Worksheet
Dim ws As Worksheet
Dim Counter As Long
On Error Resume Next
Set wsSheet = Sheets("Mucluc")
'Kiem tra su ton tai cua Sheet
On Error GoTo 0
If wsSheet Is Nothing Then
'Neu chua co thi them vao vi tri dau tien cua Workbook
Set wsSheet = ActiveWorkbook.Sheets.Add(Before:=Worksheets(1))
wsSheet.Name = "Mucluc"
End If
With wsSheet
.Cells(2, 1) = "DANH SACH CAC SHEET"
.Cells(2, 1).Name = "Index"
.Cells(4, 1).Value = "STT"
.Cells(4, 2).Value = "Ten Sheet"
End With
'Merge Cell
With Range("A2:B2")
.Merge
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
'Set ColumnWidth
With Columns("A:A")
.ColumnWidth = 8
.HorizontalAlignment = xlCenter
End With
With Range("A4")
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
Columns("B:B").ColumnWidth = 30
With Range("B4")
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
Counter = 1
For Each ws In Worksheets
If ws.Name <> wsSheet.Name Then
'Gan gia tri cot thu tu
wsSheet.Cells(Counter + 4, 1).Value = Counter
'Tao lien ket
wsSheet.Hyperlinks.Add Anchor:=wsSheet.Cells(Counter + 4, 2), _
Address:="", _
SubAddress:=ws.Name & "!A1", _
ScreenTip:=ws.Name, _
TextToDisplay:=ws.Name
'Them nut Quay ve Sheet Muc luc tai moi Sheet
With ws
.Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Quay ve"
End With
Counter = Counter + 1
End If
Next ws
Set xlSheet = Nothing
End Sub
Then click on the Run icon on the toolbar to run the code.
How to get a list of sheets in an Excel file
By doing so, a new Table of Contents sheet will automatically be created, and the list of sheets in the file will be displayed there. You only need to click on any sheet, and the worksheet will be easily switched to that sheet.
How to get a list of sheets in an Excel file
In conclusion, this article has shown you how to get a list of sheets in an Excel file. We hope that this article will be useful for you in your work. Good luck!
