Với File Excel gồm nhiều Sheet và bạn muốn tạo mục lục tới các sheet đó. Trước hết, ta cần phải lấy được tên của các sheet đó. Để thực hiện lấy tên Sheet trong Excel tự động ta có thể sử dụng hàm sau:
1. Cách lấy tên của 1 sheet hiện tại
Để lấy tên của sheet hiện tại, các bạn chỉ cần nhập công thức sau vào ô mà bạn muốn để tên của sheet đó:
=RIGHT(CELL("filename", A1), LEN(CELL("filename", A1)) - FIND("]", CELL("filename", A1)))
Lưu ý: File Excel phải được lưu (Save) thì hàm CELL(“filename”, A1) mới trả về giá trị đầy đủ (đường dẫn + tên sheet).
Giải thích công thức:
CELL("filename", A1)Trả về đường dẫn + tên file + tên sheet, ví dụ: C:\Users\…\Documents\[MyFile.xlsx]Sheet1FIND("]", ...)Xác định vị trí dấu ] trước tên sheet.RIGHT(..., LEN(...) - FIND(...))để Cắt phần sau dấu ] để lấy tên sheet.
Ngoài công thức trên, ta có thể sử dụng thêm công thức khác bằng cách kết hợp hàng CELL với hàm LEN và MID như sau:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Giải thích công thức trên:
CELL("filename",A1)trả về thông tin đầy đủ về đường dẫn, tên file và tên sheet (ví dụ: [workbook.xlsx]Sheet1).FIND("]",CELL("filename",A1))+1xác định vị trí của dấu ] và lấy phần sau nó (tức là tên sheet).MID(..., ..., 255)trích xuất tối đa 255 ký tự từ vị trí sau dấu ] để lấy tên sheet.
Lưu ý: Công thức này yêu cầu file đã được lưu để có thông tin “filename”.
2. Cách lấy danh sách các sheet hiện có trong file Excel
Để lấy danh sách các sheet hiện có trong file Excel, đầu tiên thì các bạn cần chọn thẻ Developer trên thanh công cụ. Sau đó các bạn chọn mục Visual Basic. Hoặc các bạn có thể sử dụng tổ hợp phím tắt Alt + F11 để mở cửa sổ VBA.
Lúc này, cửa sổ VBA hiện ra. Các bạn chọn thẻ Insert => Module.
Sau đó các bạn sao chép đoạn code dưới đây vào hộp thoại Module.
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
Sau đó các bạn nhấn vào biểu tượng của Run trên thanh công cụ để chạy đoạn code.
Chỉ cần như vậy là một sheet Mục lục mới sẽ tự động được tạo ra vào danh sách các sheet hiện có trong file sẽ được lấy ra tại đây. Các bạn chỉ cần nhấn vào một sheet bất kỳ là trang tính sẽ tự động được chuyển sang sheet đó một cách dễ dàng.
Như vậy, bài viết trên đã hướng dẫn các bạn cách lấy danh sách các sheet hiện có trong file Excel. Hy vọng bài viết trên sẽ hữu ích với các bạn trong quá trình làm việc, đặc biệt là hữu ích với các kế toán khi phải thực hiện nhiều file quản lý khác nhau. Chúc các bạn thành công!
Từ khóa tìm kiếm liên quan: hàm lấy tên sheet trong excel, lấy tên sheet trong excel, công thức lấy tên sheet trong excel, lấy tên tất cả các sheet trong excel, hàm lấy tên sheet.
