Cách Tạo Mục Lục Tự Động Cho Các Sheet Trong Excel

Đôi khi có những trường hợp các bạn phải làm việc với một lúc nhiều đến hàng trăm, hàng ngàn sheet trong một file Excel? Bạn đã quá mệt mỏi với việc di chuyển để đến các sheet khác nhau? Vậy thì bài viết hôm nay chắc chắn bạn không thể bỏ qua. Hãy cùng mình tìm hiểu về cách tạo mục lục tự động cho các sheet trong Excel ở bài viết ngày hôm nay nhé.

Hiển thị thẻ Developer

Thông thường thì thẻ Developer sẽ không mặc định hiển thị ở trên cùng giống như các thẻ thông dụng khác như Home, Insert,… Vậy nên để hiển thị thẻ này, các bạn hãy ấn chuột phải vào thanh ribbon rồi chọn Customize the Ribbon.

tùy chỉnh ribbon

Sau đó thì hộp thoại Excel Options sẽ hiện lên. Các bạn nhìn ngay qua bên phải sẽ thấy ô Developer chưa được chọn, các bạn ấn chọn ô đó rồi ấn OK. Lúc này thì các bạn sẽ thấy thẻ Developer ngay trên cùng.

chọn hiển thị thẻ developer

Lấy tên toàn bộ các sheet

Sau khi đã hiển thị xong thẻ Developer, chúng ta sẽ bắt đầu đi vào từng bước để tiến hành tạo mục lục tự động cho các sheet trong Excel. Đầu tiên ở thẻ Developer các bạn hãy chọn Visual Basic ở góc ngoài cùng bên trái.

mở hộp thoại visual basic

Lúc này thì hộp thoại Microsoft Visual Basic for Applications sẽ xuất hiện. Các bạn ấn chuột phải vào tên của sheet đầu tiên trong mục Microsoft Excel Objects, chọn Insert rồi chọn Module để tạo ra một module mới. Mục đích của thao tác này là tạo ra một file để viết code.

tạo module mới để viết code

Tiếp theo, chúng ra sẽ viết đoạn code đầu tiên để lấy số thứ tự cũng như tên của toàn bộ các sheet hiện đang có. Các bạn lưu ý từ bước viết code các bạn nên kết hợp theo dõi video ở phía trên để dễ dàng thao tác hơn. Đầu tiên chúng ta sẽ khai báo các biến cần thiết: wb viết tắt cho WorkBook chính là file Excel mà các bạn đang sử dụng, ws viết tắt cho WorkSheet sẽ là bảng tính hiện tại mà các bạn đang làm việc, isheet là biến đếm và irow là biến chứa giá trị là chỉ số hàng của ô. Sau khi khai báo các biến cần thiết thì tiếp theo các bạn khởi tạo bằng 2 câu lệnh bên dưới.

danh sách các biến

Sau khi đã khai báo biến và khởi tạo, kế tiếp chúng ta sẽ tạo một vòng lặp để quét qua các sheet đang có. Vòng lặp này sẽ chạy từ 2 (giá trị được gán cho biến isheet) đến tổng số lượng sheet bạn đang có. Đầu tiên với số thứ tự sẽ bằng isheet trừ đi 1 còn tên sheet được lấy bằng câu lệnh wb.Sheets(isheet).Name. Cuối cùng thì các bạn tăng irow lên 1 để ở vòng lặp tiếp theo các giá trị sẽ được áp dụng cho hàng tiếp theo bên dưới. Sau khi xong hết thì các bạn hãy ấn nút mũi tên màu xanh ở phía trên để chạy đoạn code vừa ghi nhé. Lúc này thì tại sheet mục lục của các bạn sẽ có số thứ tự và tên của các sheet rồi.

lấy tên toàn bộ các sheet

VD: isheet = 2
irow = 6
For isheet = 2 To wb.Sheets.Count
ws.Range("B" & irow).Value = isheet - 1
ws.Range("C" & irow).Value = wb.Sheets(isheet).Name
Next

Tạo liên kết đến các sheet

Bước tiếp theo chúng ta sẽ cùng tạo liên kết để dẫn đến các sheet từ mục lục, các bạn nhập vào đoạn code như hình bên dưới. Dòng đầu tiên sẽ chọn worksheet hiện tại, tiếp theo là chọn ô để chèn liên kết tại đó và dòng cuối cùng là để tạo một liên kết đến sheet với nội dung mình nhập là “go to sheet”. Sau khi xong hết thì các bạn hãy ấn nút mũi tên màu xanh ở phía trên để chạy đoạn code vừa ghi nhé.

tạo liên kết đến các sheet

VD: ws.Select
ws.Range("D" & irow).Select
ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wb.Sheets(isheet).Name & "'!A1", TextToDisplay:="go to sheet"

Tạo nút quay về mục lục từ các sheet

Sau khi đã tạo được liên kết, các bạn nên tạo thêm một nút để quay về mục lục tại các sheet đó để tránh trường hợp các bạn có hàng trăm, hàng ngàn sheet. Lúc này thì các bạn phải mất rất nhiều thời gian để trờ về sheet mục lục đầu tiên. Với nút trở về các bạn tạo khối bằng cách ở thẻ Insert chọn Shapes rồi chọn một khối bất kỳ, vì nút mang ý nghĩa là trờ về nên mình sẽ chọn khối hình mũi tên.

chọn khối để tạo nút trở về mục lục

Để nhập nội dung cho khối vừa tạo các bạn nhấp chuột phải vào khối đó, chọn Edit Text rồi nhập nội dung mình mong muốn. Ngoài ra các bạn cũng có thể chỉnh sửa kích cỡ, tô màu, căn hàng cho nội dung này nữa nhé.

ghi nội dung cho khối

Bước tiếp theo khá quan trọng, các bạn cần phải biết được tên của khối vừa tạo là gì bởi vì nó sẽ cần thiết cho phần code mà chúng ta sắp thực hiện. Để xem được tên của khối, các bạn ấn chọn vào khối rồi nhìn vào ô nằm ở góc trên bên trái sheet hiện tại.

nhìn tên của khối vừa tạo

Để tạo liên kết cho khối thì đầu tiên các bạn nhấp chuột phải vào khối rồi chọn Link.

tạo liên kết cho khối

Lúc này thì hộp thoại Insert Hyperlink sẽ hiện ra. Các bạn chọn Place in This Document rồi kéo lên đầu để chọn đúng sheet mục lục của mình. Sau khi xong các bạn ấn OK để tạo liên kết.

chọn liên kết đến sheet mục lục

Xong những bước phía trên thì các bạn đã tạo xong một nút để trở về sheet mục lục, tuy nhiên lúc này ở các sheet khác vẫn chưa có. Vậy nên chúng ta phải sử dụng thêm 1 đoạn code để có thể tạo được những nút tương tự ở các sheet còn lại. Trong đoạn code ở hình bên dưới, dòng đầu tiên là để chọn nút mà ta đã tạo ở những bước trên (lúc này đã có liên kết trở về mục lục), dòng thứ 2 là thực hiện lệnh sao chép, dòng thứ 3 là chọn sheet hiện tại (theo giá trị của isheet trong từng vòng lặp), dòng thứ 4 là chọn ô và dòng cuối cùng là lệnh dán nút vào ô được chọn ở dòng 4. Sau khi xong hết thì các bạn hãy ấn nút mũi tên màu xanh ở phía trên để chạy đoạn code vừa ghi nhé. Chúc các bạn thực hành tốt!

code để copy khối qua các sheet khác