Tạo mục lục tự động trong excel?

Liên hệ QC

LikeIt

Thành viên tiêu biểu
Tham gia
16/6/06
Bài viết
415
Được thích
254
Nghề nghiệp
Others
Kính gửi các anh chị,
Em đọc trên một báo nào đó (thực sự ko nhớ) có nói về một VD trong excel là tạo mục lục tự động trong một sheet, để di chuyển các sheet trong bảng tính cho dễ dàng, nhưng khi thực hiện theo chỉ dẫn thì ko đc, ko biết em hiểu có đúng ko? vì em nghĩ khi tạo đc mục lục tự động thì khi thêm hoặc bỏ tên một sheet nào trong file này thì nó sẽ hiện lên trên sheet MUCLUC? mong các anh chị biết về VBA chỉ giúp. Em xin post VD của em làm và nội dung bài báo đã sưu tầm.tks - NHT
Tạo mục lục các Sheet trong Excel 2003


Đây là một thủ thuật mà mình đánh giá là...4 sao cho tính tiện dụng...

Vấn đề:
Khi làm việc với một tập tin Excel (Worbook) có nhiều worksheet (bảng tính), các bạn sẽ gặp nhiều khó khăn khi di chuyển giữa các bảng tính này.

Giải pháp:
Chúng ta sẽ tạo ra một Sheet mới với tên là MụcLục. Sau đó thêm một đoạn code vào Sheet này để tạo mục lục tự động.
{ad_200x200_right}
Các bước thực hiện:
1. Trở về Sheet đầu tiên. Vào Insert > Worksheet. Đặt tên của Sheet này lại thành MụcLục hay để nguyên tùy thích.
2. Right-click lên thẻ của Sheet này chọn View Code.
3. Copy tất cả đoạn Code này vào:
PHP:
Private Sub Worksheet_Activate() 
Dim wSheet As Worksheet 
Dim M As Long 
M = 1 
    With Me 
        .Columns(1).ClearContents 
        .Cells(1, 1) = "INDEX" 
        .Cells(1, 1).Name = "Index" 
    End With 
    
    For Each wSheet In Worksheets 
        If wSheet.Name <> Me.Name Then 
        M = M + 1 
        With wSheet 
            .Range("H1").Name = "Start" & wSheet.Index 
            .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index" 
        End With 
            Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name 
        End If 
        Next wSheet 
End Sub
4. Nhấn Alt + Q và Save lại.
5. Từ Excel, vào Tool > Macro > Security. Trong hộp thoại này, chọn mức Low. Vì trong file Excel của bạn có dùng Macro, nên đôi khi mức Macro security ở mức high, đoạn lệnh của bạn sẽ không có tác dụng. Nếu bạn chọn Mediup, bạn sẽ được nhắc là chạy macro không. Trong trường hợp này, bạn chọn Enable Macro để thấy tác dụng.
5. Nhấn Ctrl + W để đóng lại Workbook và mở lại Workbook này.
Đến đây, khi nhấp vào Sheet có tên là MụcLục, bạn sẽ thấy danh sách các bảng tính hiện ra. Nhấp vào Sheet nào mà bạn muốn di chuyển nhanh đến để làm việc....
 

File đính kèm

  • Mucluc.rar
    4.8 KB · Đọc: 4,806
Chỉnh sửa lần cuối bởi điều hành viên:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M
As Long
M
= 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
M
= M + 1
With wSheet
.Range("H1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
End With
Me
.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End
If
Next wSheet
End Sub

đoạn code trên chạy rất tốt nhưng khi có nhiều sheet và nhiều hàm thì khi đến các sheet thì rất nhanh nhưng khi trởvề index nó chậm quá có giải pháp nào cho nhanh lên đôợc không Anh Tuấn.
 
Upvote 0
Cảm ơn bạn. Tuy nhiên em muốn đổi index thành "Trang chủ" và "Quay lại trang chủ" mà trên macro toàn báo lỗi
Anh chị giúp em với. em đang cần gấp!
 
Upvote 0
Cảm ơn bạn. Tuy nhiên em muốn đổi index thành "Trang chủ" và "Quay lại trang chủ" mà trên macro toàn báo lỗi
Anh chị giúp em với. em đang cần gấp!
 
Upvote 0
--=0Cảm ơn bạn. Tuy nhiên em muốn đổi index thành "Trang chủ" và "Quay lại trang chủ" mà trên macro toàn báo lỗi
Anh chị giúp em với. em đang cần gấp!
 
Upvote 0
Bạn anhtuan ơi, cho mình hỏi là file LinktoSheet của bạn khi muốn quay về Sheet Index thì làm thế nào.
 
Upvote 0
Vì em cho code vào This Worbook nên nó ko chạy là phải rồi...
Hướng dẩn có đoạn: Vậy em cắt đoạn code ấy và cho nó vào Sheet Index đi, nó sẽ chạy ngay lập tức

He... he...
Món này ko cần dùng VBA vẩn làm dc... Dùng công thức quá dể luôn...
Dựa trên nền file GetSheetName (May quá, nghĩ chơi chơi mà giờ có cái để ứng dụng đây)
Xem file... Sheet Index sẽ tự động cập nhật bất kể ta đổi tên sheet, xóa sheet hay thêm sheet gì nó cũng chơi tuốt.... Tất cả chỉ dùng chức năng Hyperlink và các name trong Define Name... 1 Name Link duy nhất nó có thể hiểu dc bạn đang muốn link đến Sheet nào... he...he.. 1 phát 1 cho tất cả...
ANH TUẤN
Anh Tuấn cho mình hỏi tại sao file LinktoSheet của anh mở được ở máy này, nhưng máy khác lại báo lỗi #N/A? Làm cách nào để file này mở được ở bấy kỳ máy nào mà ko báo lỗi ạ?
Thanks anh!
 
Upvote 0
em là thành viên mới! em không biết hỏi ở đâu? cho e vào đây xin hỏi 1 tí!
em có 1 tập tin A trong đó co 5 sheet. nhưng e cảm thấy nhiều rồi, nên e copy tập tin A ra 1 chỗ khác
(tạm gọi là tập tin B). e đã bỏ 4 sheet trong đó. chừa lại 1 sheet cuối cùng, rồi xóa hết chừa lại tháng 12/2013 , nhưng khi e sữa tháng 12 thành tháng 01 rồi làm trên đó,xong hết! khi e quay lại tập tin A tìm lại thì hỡi ôi, nó mất hết và có nội dung y chang nội dung tập tin B mà e đã sửa
Vậy là sao các a c! có cách nào lấy lại không? xin giúp e với!! cảm ơn rất nhiều!!!
 
Upvote 0
tôi làm vậy sao khi save thi no lai báo:the following features cannot be saved in macofree worbook:
.VB project
chưa save được thi làm sao mà chọn maco enable
 
Upvote 0
Tạo mục lục vào từng vùng

Tôi đã xem chủ đề này: http://www.giaiphapexcel.com/forum/showthread.php?8798-Tạo-mục-lục-tự-động-trong-excel
Nhưng để hiểu và viết code vba tùy biến sang trường hợp của mình thì tôi chưa làm được. Mong mọi người giúp tôi vến đề tạo mục lục tự động vào vùng dữ liệu trên bảng tính trong file đính kèm.
Chỉ cập nhật những sheet có tên T1; T2; ..... con các sheet có tên khác tên (T1; T2; ..... ) thì không cập nhật
Tôi xin cảm ơn rất nhiều
 

File đính kèm

  • Link_Sheet.xls
    43 KB · Đọc: 31
Lần chỉnh sửa cuối:
Upvote 0
Rất mong được mọi người giúp đỡ.!.
 
Upvote 0
Xin Chào các Anh chị và tất cả mọi người trên GPE.
- Em muốn tạo Sheet menu, từ các ô của sheet menu (được tô màu vàng ở trong file đính kèm bài #49) này hiện tên các sheet T1; T2; .... để khi bấm vào thì linh đến các sheet có cùng tên là T1; T2; .... đó và tại các sheet T1; T2; .... tại ô R1. khi bấm vào ô R1 từ các sheet T1; T2; .... thì quay trở lại sheet Menu (Chỉ các sheet có tên được bắt đầu bởi chữ T), khi cập nhật các sheet có tên T thì tự động thêm vào trong vùng được tô màu vàng trong file đính kèm bài #49
- Em gửi chủ đề đã hơn một ngày nay mà chưa được hồi âm, em rất mong được cac bác quan tâm giúp em
Em xin chan thành cảm ơn các bác và tất cả mọi người!./.
 
Lần chỉnh sửa cuối:
Upvote 0
Xin Chào các Anh chị và tất cả mọi người trên GPE.
- Em muốn tạo Sheet menu, từ các ô của sheet menu (được tô màu vàng ở trong file đính kèm bài #49) này hiện tên các sheet T1; T2; .... để khi bấm vào thì linh đến các sheet có cùng tên là T1; T2; .... đó và tại các sheet T1; T2; .... tại ô R1. khi bấm vào ô R1 từ các sheet T1; T2; .... thì quay trở lại sheet Menu (Chỉ các sheet có tên được bắt đầu bởi chữ T), khi cập nhật các sheet có tên T thì tự động thêm vào trong vùng được tô màu vàng trong file đính kèm bài #49
- Em gửi chủ đề đã hơn một ngày nay mà chưa được hồi âm, em rất mong được cac bác quan tâm giúp em
Em xin chan thành cảm ơn các bác và tất cả mọi người!./.

trong file kèm, bạn xem đúng ý chưa

Tên she et được xác định theo quy luật công thức, bạn có thể copy tiếp ra (lưu ý dòng liền dong -10 dòng, cột cách nhau 4 cột) là được

Hiện tên sheet xác định theo ta lập công thức, còn muốn tự động lấy cả tên sheet nữa thì phải dùng macro4 phức tạp hơn chút - khi đó ta đổi tên sheet thì tên she et ở ô link cũng đổi theo,
 

File đính kèm

  • Link_Sheet.xls
    55 KB · Đọc: 77
Lần chỉnh sửa cuối:
Upvote 0
trong file kèm, bạn xem đúng ý chưa

Tên she et được xác định theo quy luật công thức, bạn có thể copy tiếp ra (lưu ý dòng liền dong -10 dòng, cột cách nhau 4 cột) là được

Hiện tên sheet xác định theo ta lập công thức, còn muốn tự động lấy cả tên sheet nữa thì phải dùng macro4 phức tạp hơn chút - khi đó ta đổi tên sheet thì tên she et ở ô link cũng đổi theo,
- Em cảm ơn bác vodoi2x. Công thức của bác rất hay và đáp ứng được yêu cầu trong trường hợp các sheet cần link đến đã có trong file. Khi File được cập nhật thêm các sheet (Txy) bất kỳ nào đó thì không được vì tại ô R1 của các Sheet mới cập nhật đó khi bấm vào không quay trờ về Sheet Menu được.
- Mỗi lần các sheet T.... này được cập nhật thì được hiển thị vào đúng các ô theo thứ tự từ bé đến lớn, những ô ứng với tên sheet mà chưa được cập nhật thì bỏ trống. Số lượng các sheet có tên bắt đầu bằng chữ T của em là 50 sheet.
- Em mong được bác và mọi người giúp em bằng VBA với để em có thể học hỏi với, em đang chuộng món VBA này.
- Em chân thành cảm ơn bác và tất cả mọi người !.
 
Lần chỉnh sửa cuối:
Upvote 0
- Em cảm ơn bác vodoi2x. Công thức của bác rất hay và đáp ứng được yêu cầu.
- Em mong được bác và mọi người giúp em bằng VBA với để em có thể học hỏi với, em đang chuộng món VBA này.
- Em chân thành cảm ơn bác và tất cả mọi người !.

VBA thì còn đơn giản hơn nhiều, list tên sheet lên

Bạn muốn học code hay ứng dụng, học thì bạn có thể đọc các bài trên rồi tự làm, tự rút ra kinh nghiệm, nếu có gì thắc mắc thì post lên hỏi là xong
 
Upvote 0
VBA thì còn đơn giản hơn nhiều, list tên sheet lên

Bạn muốn học code hay ứng dụng, học thì bạn có thể đọc các bài trên rồi tự làm, tự rút ra kinh nghiệm, nếu có gì thắc mắc thì post lên hỏi là xong
Em cảm ơn bác.
Trước mắt là để ứng dụng ngay và sau đó là học hỏi mà chẳng biết có học được không Bác ạ, vì em cũng xem hết chủ đề này mà Thấy mình chưa làm được theo ý mình.
Bác giúp em code để em ứng dụng, sau nếu có gì vướng mắc em sẽ hỏi bác và mọi người.
Em xin cảm ơn Bác và mọi người nhiều!.
 
Lần chỉnh sửa cuối:
Upvote 0
Em cảm ơn bác.
Trước mắt là để ứng dụng ngay và sau đó là học hỏi mà chẳng biết có học được không Bác ạ, vì em cũng xem hết chủ đề này mà Thấy mình chưa làm được theo ý mình.
Bác giúp em code để em ứng dụng, sau nếu có gì vướng mắc em sẽ hỏi bác và mọi người.
Em xin cảm ơn Bác và mọi người nhiều!.


Vậy nháy phải lên tab sheet Menu và chọn View code - rồi copy paste code sau vào ,

sau đó save file (nhớ phải enable macro)


PHP:
Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    
    Const cDiaChiDatMenu = "B10"
    Const cDiaChiDatBackToMenu = "R1"
    
    Dim wSheet As Worksheet, CEL As Range, k As Long
    Set CEL = Me.Range(cDiaChiDatMenu)
    CEL.Resize(1000, 100).ClearContents
    CEL.Value = "MENU"
    k = 0
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name And wSheet.Name Like "T*" Then
            k = k + 1
            With wSheet
                .Hyperlinks.Add Anchor:=.Range(cDiaChiDatBackToMenu), Address:="", SubAddress:="'" & Me.Name & "'!" _
                                            & cDiaChiDatMenu, TextToDisplay:="Back to Menu"
                Me.Hyperlinks.Add Anchor:=CEL.Offset((k - 1) Mod 10 + 1, 4 * ((k - 1) \ 10)), Address:="", SubAddress:="'" & .Name & "'!" _
                                            & cDiaChiDatBackToMenu, TextToDisplay:=wSheet.Name
            End With
        End If
    Next wSheet
End Sub

Chú ý nếu cần thì thay đổi địa chỉ các ô tại 2 dòng này

PHP:
Const cDiaChiDatMenu = "B10"
Const cDiaChiDatBackToMenu = "R1"

thế là xong,
 
Lần chỉnh sửa cuối:
Upvote 0
Vậy nháy phải lên tab sheet Menu và chọn View code - rồi copy paste code sau vào ,

sau đó save file (nhớ phải enable macro)


PHP:
Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    
    Const cDiaChiDatMenu = "B10"
    Const cDiaChiDatBackToMenu = "R1"
    
    Dim wSheet As Worksheet, CEL As Range, k As Long
    Set CEL = Me.Range(cDiaChiDatMenu)
    CEL.Resize(1000, 100).ClearContents
    CEL.Value = "MENU"
    k = 0
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name And wSheet.Name Like "T*" Then
            k = k + 1
            With wSheet
                .Hyperlinks.Add Anchor:=.Range(cDiaChiDatBackToMenu), Address:="", SubAddress:="'" & Me.Name & "'!" _
                                            & cDiaChiDatMenu, TextToDisplay:="Back to Menu"
                Me.Hyperlinks.Add Anchor:=CEL.Offset((k - 1) Mod 10 + 1, 4 * ((k - 1) \ 10)), Address:="", SubAddress:="'" & .Name & "'!" _
                                            & cDiaChiDatBackToMenu, TextToDisplay:=wSheet.Name
            End With
        End If
    Next wSheet
End Sub

Chú ý nếu cần thì thay đổi địa chỉ các ô tại 2 dòng này

PHP:
Const cDiaChiDatMenu = "B10"
Const cDiaChiDatBackToMenu = "R1"

thế là xong,
Em cảm ơn bác nhiều!.
Code của Bác như vậy là rất ngon lành rồi.
Thưa bác và các Thành viên của GPE.
- Các Sheet T1; T2; ...... T50 này là em đặt tên cho nó như vậy để tiện quản lý cho ngắn gọn. Tên Thật của nó dài thoằng và khác nhau được các nơi gửi về cho em và không theo thứ tự từ T1 đến T50 vì thế mà danh sách tên thật của nó được em nhập trước theo thứ tự từ 1 đến 50 tương ưng vào vùng như hình em đính kèm; để khi nhận được sheet các nơi gửi về để em biết mà em Rename thành Sheet có tên tương ứng.
Như bài #53 em có trình bày
Mỗi lần các sheet T.... này được cập nhật thì được hiển thị vào đúng các ô theo thứ tự từ bé đến lớn, những ô ứng với tên sheet mà chưa được cập nhật thì bỏ trống. Số lượng các sheet có tên bắt đầu bằng chữ T của em là 50 sheet.
- Như hình sau Sheet Menu.JPG
Bác và mọi người giúp em với!.
Em xin chân thành cảm ơn!.
 
Lần chỉnh sửa cuối:
Upvote 0
Em cảm ơn bác nhiều!.
Code của Bác như vậy là rất ngon lành rồi.
Thưa bác và các Thành viên của GPE.
- Các Sheet T1; T2; ...... T50 này là em đặt tên cho nó như vậy để tiện quản lý cho ngắn gọn. Tên Thật của nó dài thoằng và khác nhau được các nơi gửi về cho em và không theo thứ tự từ T1 đến T50 vì thế mà danh sách tên thật của nó được em nhập trước theo thứ tự từ 1 đến 50 tương ưng vào vùng như hình em đính kèm; để khi nhận được sheet các nơi gửi về để em biết mà em Rename thành Sheet có tên tương ứng.
Như bài #53 em có trình bày

- Như hình sau View attachment 117345
Bác và mọi người giúp em với!.
Em xin chân thành cảm ơn!.


Vậy thì bạn cải tiến code trên mà dùng (???)

Tôi thì không hiểu chỗ bạn cho tên sheet thật và T thì khác nhau chỗ nào, và cắt nghiã việc

Mỗi lần các sheet T.... này được cập nhật thì được hiển thị vào đúng các ô theo thứ tự từ bé đến lớn, những ô ứng với tên sheet mà chưa được cập nhật thì bỏ trống. Số lượng các sheet có tên bắt đầu bằng chữ T của em là 50 sheet.
thì cái gì là "... thứ tự từ bé đến lớn...", bé/ lớn so với cái gì,

và các T ... tên sheet thật bạn sẽ gõ vào ah?

tên sheet thật là gì? khi bạn đã đổi tên sheet về T# thì đó là tên sheet hiện thời ? (nếu khác, thì bạn nên post file theo ý mới của bạn)???
 
Lần chỉnh sửa cuối:
Upvote 0
Vậy thì bạn cải tiến code trên mà dùng (???)

Tôi thì không hiểu chỗ bạn cho tên sheet thật và T thì khác nhau chỗ nào, và cắt nghiã việc


thì cái gì là "... thứ tự từ bé đến lớn...", bé/ lớn so với cái gì,

và các T ... tên sheet thật bạn sẽ gõ vào ah?

tên sheet thật là gì? khi bạn đã đổi tên sheet về T# thì đó là tên sheet hiện thời ? (nếu khác, thì bạn nên post file theo ý mới của bạn)???
Do em giải thích chưa được hợp lý lắm, Có khi càng làm rối thêm vấn đề!.
Em xin trình bày lại như sau:
- Vùng em tô màu vàng ở trên hình ở bài #57 đó là 5 vùng tương ứng mỗi vùng là 10 sheet theo thứ tự từ T1..... đến T10; tiếp đến vùng 2 là từ T11 .... đến T21; cứ như vậy theo thứ tự các sheet từ T1 đến T50 được hiển thị ở các vùng đó.
- Như vậy khi các sheet từ T1 đến T50 mà chưa có ở trong file thì ô đó được bỏ trống; như trên hình là ô chưa T2 bỏ trống; T8 bỏ trống và T18 bỏ trống. Khi nào có trong file thì được cập nhật vào đúng vị trí của nó.
Em xin cảm ơn bác nhiều!./.
 
Lần chỉnh sửa cuối:
Upvote 0
Do em giải thích chưa được hợp lý lắm, Có khi càng làm rối thêm vấn đề!.
Em xin trình bày lại như sau:
- Vùng em tô màu vàng ở trên hình ở bài #57 đó là 5 vùng tương ứng mỗi vùng là 10 sheet theo thứ tự từ T1..... đến T10; tiếp đến vùng 2 là từ T11 .... đến T21; cứ như vậy theo thứ tự các sheet từ T1 đến T50 được hiển thị ở các vùng đó.
- Như vậy khi các sheet từ T1 đến T50 mà chưa có ở trong file thì ô đó được bỏ trống; như trên hình là ô chưa T2 bỏ trống; T8 bỏ trống và T18.
Em xin cảm ơn bác nhiều!./.

Vậy thì đơn giản dùng code này (Right click lên tab sheet MENU và copy code này , nếu cso code cũ thì xoá đi) sẽ được như ý

bạn có thể xoá , cập nhập thêm các sheet và đổi tên thành dạng T# thì Menu sẽ được cập nhật


PHP:
Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    
    Const cDiaChiDatMenu = "B10"
    Const cDiaChiDatBackToMenu = "R1"
    
    Dim wSheet As Worksheet, CEL As Range, k As Long
    Set CEL = Me.Range(cDiaChiDatMenu)
    CEL.Offset(1).Resize(1000, 100).ClearContents   ''  CEL.Value = "MENU"
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name And wSheet.Name Like "T*" Then
            With wSheet
                On Error GoTo nextFOR
                k = CLng(Replace(.Name, "T", ""))
                .Hyperlinks.Add Anchor:=.Range(cDiaChiDatBackToMenu), Address:="", _
                    SubAddress:="'" & Me.Name & "'!" & cDiaChiDatMenu, TextToDisplay:="MENU"
                Me.Hyperlinks.Add Anchor:=CEL.Offset((k - 1) Mod 10 + 1, 4 * ((k - 1) \ 10)), Address:="", _
                    SubAddress:="'" & .Name & "'!" & cDiaChiDatBackToMenu, TextToDisplay:=wSheet.Name
            End With
        End If
nextFOR: Err.Clear
    Next wSheet
End Sub
 
Upvote 0
Web KT
Back
Top Bottom