Macro copy sheet theo tên cho trước

Bảng tính Excel của bạn có nhiều sheet, các bạn muốn quản lý chúng một cách khoa học nhất để khi cần thiết có thể nhanh chóng mở ra. Để quản lý các sheet nhanh chóng và đơn giản nhất thì các bạn cần tạo mục lục danh sách các sheet trong file Excel.

Dưới đây giới thiệu đến các bạn hai cách tạo mục lục danh sách các sheet trong Excel.

Cách 1: Tạo mục lục danh sách các sheet thủ công.

Bước 1: Tạo một sheet mới nên đặt ở đầu các sheet khác, tại đây các bạn tạo danh sách tên các sheet theo thứ tự bất kỳ mà bạn muốn.

Bước 2: Chọn tên sheet đầu tiên trong danh sách bạn vừa tạo, nhấn chuột phải và chọn Hyperlink (hoặc tổ hợp phím Ctrl + K).

Bước 3: Xuất hiện hộp thoại Insert Hyperlink, các bạn chọn Place in This Document trong phần Link to, sau đó các bạn chọn đến tên sheet các bạn muốn dẫn đến và nhấn OK để hoàn tất.

Thực hiện các bước tương tự với các tên sheet dưới, cuối cùng bạn sẽ được mục ục danh sách các sheet trong Excel. Các bạn chỉ cần nhấn chuột vào tên sheet thì sheet bạn chọn sẽ được mở.

Nhưng đây là cách các bạn có thể sử dụng với những file Excel có ít sheet, nếu có nhiều sheet các bạn nên thực hiện cách dưới đây.

Cách 2: Sử dụng VBA trong Excel.

Bước 1: Trên file Excel cần tạo mục lục danh sách các sheet các bạn nhấn tổ hợp phím Alt + F11 để mở Visual Basic Editor.

Bước 2: Trên cửa sổ Microsoft Visual Basic for Applications các bạn chọn Insert -> Module.

Bước 3: Copy toàn bộ đoạn code dưới đây và paste vào khung 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

Bước 4: Sau đó các bạn nhấn chọn Run -> Run Sub hoặc phím F5 để chạy chương trình.

Lúc này trên file Excel của bạn sẽ có một sheet Mucluc và trong đó là mục lục danh sách các sheet trong Excel. Các bạn muốn mở sheet nào thì các bạn chỉ cần nhấn con trỏ chuột vào tên sheet đó trong danh sách các sheet.

Như vậy với hai cách tạo mục lục danh sách các sheet trong Excel, hi vọng các bạn có thể áp dụng dễ dàng với file Excel của mình. Chúc các bạn thành công!

Trong bài học này chúng ta cùng tìm hiểu về tham chiếu tới Sheet trong VBA. Đây là một đối tượng thường xuyên được sử dụng trong khi làm việc trên VBA, vì để xác định chính xác địa chỉ mà chúng ta muốn thao tác tới thì cần xác định rõ địa chỉ đó ở Sheet nào, vùng (range) nào.

Có những cách tham chiếu tên Sheet cơ bản như sau:

• Tham chiếu tên Sheet (Sheets Name – thường gặp qua Record Macro)
• Tham chiếu Tên Code của Sheets (Sheets Code Name)

Tham chiếu tên Sheet (Sheets Name – thường gặp qua Record Macro)

Khi sử dụng chức năng Record Macro để tự học VBA qua các code tự động tạo qua quá trình ghi lại thao tác người dùng, chúng ta có thể thấy cách tham chiếu tên Sheet qua thao tác này như sau:

Chúng ta thấy ở đây Excel đã chọn đúng tên Sheet theo SheetTab trong Excel:

Sheet1 có tên là Vidu1 => Khi chọn Sheet này thì VBA sẽ ghi nhận là Sheets(‘Tên sheet”).Select

* Ưu điểm của cách này là chúng ta có thể dễ dàng xác định vị trí các Sheet thông qua việc căn cứ trực tiếp vào tên Sheet đang có tại Sheet Tab.

* Nhược điểm là nếu có sự thay đổi tên sheet tại Sheet Tab thì không tự thay đổi tên tham chiếu trong VBA => Gây lỗi => Phải sửa lại trong VBA

Tham chiếu Tên Code của Sheets (Sheets Code Name)

Kỹ thuật này thường gặp bởi người đã hiểu rõ về lập trình trong VBA và đã nắm được các ưu nhược điểm của tham chiếu Tên Sheet, từ đó chuyển sang tham chiếu tới Code Name của Sheet

Mỗi Sheet trong Workbook được mã hóa bởi 1 CodeName duy nhất, ít thay đổi, chỉ thay đổi được trong môi trường VBA, còn việc thay đổi tại SheetTab không làm ảnh hưởng tới CodeName này

Trong hình trên, chúng ta thấy phần Code Name của Sheet là phần bên trái, còn phần tên Sheet trong Sheet tab là phần trong dấu ngoặc.

Khi tham chiếu tới Sheet trong VBA, chúng ta nên sử dụng phần Code Name để tham chiếu.

Cách tham chiếu như sau:

Sheet1.Select     ‘Tham chiếu trực tiếp tới CodeName của Sheet

hoặc Sheets(1).Select => Tham chiếu tới số trong CodeName (Index Number)

* Sheets Code Name có thể thay đổi được ở mục Properties/(Name) của Sheet đó

Sau khi đổi tên Sheet chúng ta có thể sử dụng tham chiếu như sau:

Đến đây bạn đã có thể bắt đầu tìm hiểu những điều thú vị của VBA được rồi. VBA giúp ích rất nhiều trong công việc: giúp bạn tăng tốc trong quá trình xử lý trên Excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như Word, Power Point, Outlook … giúp biến file Excel của bạn trở thành 1 phần mềm chuyên nghiệp …

Để có thể học VBA một cách đầy đủ, có hệ thống, bạn hãy tham gia khoá học VBA101 – VBA cơ bản dành cho người mới bắt đầu của hệ thống Học Excel Online. Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký khóa học này. Chi tiết xem tại:

Tác giả: duongquan211287

· · ·