Tạo mục lục danh sách các Sheet trong Excel

(Thủ thuật VBA Excel)

- Trong Excel khi làm việc với một vài Sheet thì việc di chuyển khá dễ dàng, người sử dụng không phải mất quá nhiều thời gian để lựa chọn Sheet cần thao tác. Tuy nhiên khi Workbook có nhiều Sheet thì việc chọn Sheet để làm việc thì quả thực rất khó và mất thời gian. Có 1 cách để di chuyển qua lại giữa các Sheet bằng sử dụng phím tắt đó là: Ctrl-Page Up: Tiến lên 1 sheet (Sheet 1 sang Sheet 2), Ctrl-Page Down: Lùi về 1 sheet (Sheet 3 về Sheet 2). 

Hoặc cũng có nhiều người sử dụng 1 Sheet chính, trong Sheet này sẽ tạo danh mục các Sheet dạng mục lục và liên kết đến đây khi kích vào. Với cách làm này người sử dụng có thể dễ dàng trong việc làm việc và di chuyển giữa các Sheet và quay về Sheet chính.  Hiện mọi người vẫn sử dụng phương pháp thủ công để làm công việc này, hôm nay Kênh phần mềm việt sẽ giới thiệu với mọi người 1 phương pháp để làm nhanh công việc này bằng cách sử dụng VBA.

Hàm này sẽ có chức năng như sau:
- Kiểm tra xem đã có Sheet mục lục chưa, nếu chưa có thì sẽ tạo mới và chèn vào vị trí đầu tiên
- Thiết lập độ rộng cột
- Định dạng các vùng dữ liệu
- Đánh số thứ tự và chèn tên Sheet vào các ô
- Chèn liên kết đến các Sheet tương ứng với từng Sheet
- Chèn một liên kết để quay về Sheet Mucluc

Dưới đây là các bước để tạo Macro này.
1. Khởi động Microsoft Excel.
2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor.
Vào Menu Inser\Module

3. Nhập toàn bộ đoạn mã sau vào

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

4. Chạy Marco vừa tạo, bạn sẽ có 1 danh sách toàn bộ các Sheet có trên Workbook hiện tại được đưa vào trong Sheet Mucluc, và từ đây bạn dễ dàng đến bất kỳ một Sheet nào khi cần.

Kênh phần mềm việt