Koen có một sổ làm việc chứa 150 trang tính, một trang tính cho mỗi chi nhánh công ty. Anh ta cần sắp xếp các trang tính đó dựa trên khu vực mà mỗi chi nhánh thuộc về. (Có năm khu vực trong công ty của anh ấy.) Anh ấy cũng lưu ý rằng sẽ thật tuyệt nếu màu tab cho mỗi trang tính có thể phản ánh khu vực và tự hỏi liệu có thể sắp xếp như vậy trong Excel hay không.

Có, phân loại như vậy là có thể, nhưng nó yêu cầu sử dụng macro.

Phần khó nhất của việc tạo macro là xác định cách bạn xác định các chi nhánh ở khu vực nào. Có lẽ cách đơn giản nhất để làm điều này là đảm bảo các trang tính của bạn sử dụng mẫu đặt tên bao gồm cả thông tin khu vực và chi nhánh. Ví dụ: bạn có thể đặt tên trang tính như “Reg01-Branch123”. Sau đó, macro của bạn có thể bước qua từng trang tính và thực hiện cả việc tô màu và sắp xếp.

Sub SortWorksheets()

Dim iReg As Integer     Dim I As Integer     Dim J As Integer     Dim K As Integer     Dim ws As Worksheet

Application.ScreenUpdating = False

' Set tab colors     For Each ws in Worksheets         iReg = Val(Mid(ws.Name,4,2))

Select Case iReg             Case 1                 ws.Tab.Color = vbRed             Case 2                 ws.Tab.Color = vbYellow             Case 3                 ws.Tab.Color = vbBlue             Case 4                 ws.Tab.Color = vbGreen             Case 5                 ws.Tab.Color = vbCyan             Case Else                 ws.Tab.ColorIndex = xlColorIndexNone         End Select     Next ws

' Sort the worksheets     For I = 1 To Sheets.Count - 1         K = I         For J = I + 1 To Sheets.Count             If UCase(Sheets(K).Name) > UCase(Sheets(J).Name) Then K = J         Next J         If K <> I Then Sheets(K).Move Before:=Sheets(I)

Next I

Application.ScreenUpdating = True End Sub

Macro hoạt động thông qua bộ sưu tập Trang tính hai lần. Trong lần chuyển đầu tiên, số vùng được trích xuất từ ​​tên của trang tính.

Điều này sau đó được sử dụng (trong cấu trúc Chọn trường hợp) để đặt màu tab.

Nếu số vùng nằm ngoài phạm vi 1-5, thì màu tab không được thực hiện.

Nếu việc xác định chi nhánh của bạn ở khu vực nào phức tạp hơn, thì bạn sẽ cần điều chỉnh macro cho phù hợp. Ví dụ: bạn có thể có một trang tính có tên “Khoá Vùng”, trong cột A, tên của từng chi nhánh của bạn và trong cột B, số vùng tương ứng cho từng chi nhánh đó. Giả sử rằng dữ liệu thực tế bắt đầu từ hàng 2, bạn có thể sửa đổi macro theo cách sau:

Sub SortWorksheets2()

Dim sTemp As String     Dim iReg As Integer     Dim I As Integer     Dim J As Integer     Dim K As Integer     Dim ws As Worksheet     Dim key As Worksheet

Application.ScreenUpdating = False

Set key = Worksheets("Region Key")



' Set tab colors     For Each ws in Worksheets         sTemp = UCase(ws.Name)

I = 2    ' Beginning row number         iReg = 0         While key.Cells(I, 1) > ""

If UCase(key.Cells(I, 1)) = sTemp Then iReg = key.Cells(I, 2)

I = I + 1         Wend         Select Case iReg             Case 1                 ws.Tab.Color = vbRed             Case 2                 ws.Tab.Color = vbYellow             Case 3                 ws.Tab.Color = vbBlue             Case 4                 ws.Tab.Color = vbGreen             Case 5                 ws.Tab.Color = vbCyan             Case Else                 ws.Tab.ColorIndex = xlColorIndexNone         End Select     Next ws

' Sort the worksheets     For I = 1 To Sheets.Count - 1         K = I         For J = I + 1 To Sheets.Count             If UCase(Sheets(K).Name) > UCase(Sheets(J).Name) Then K = J         Next J         If K <> I Then Sheets(K).Move Before:=Sheets(I)

Next I     Sheets("Region Key").Move Before:=Sheets(1)



Application.ScreenUpdating = True End Sub

Sự khác biệt lớn nhất giữa macro này và macro trước đó là macro này lấy số vùng từ một trang tính. Là tác vụ cuối cùng trong macro, trang tính “Khoá Vùng” được chuyển đến đầu bộ sưu tập Trang tính.

Lưu ý rằng macro thứ hai này cũng có thể dẫn đến các tab trang tính của bạn, ở cuối cửa sổ chương trình, trông giống như kính vạn hoa có màu sắc.

Lý do cho điều này là các tab được sắp xếp dựa trên tên của chúng, không dựa trên màu sắc của chúng. Điều này khác với macro đầu tiên, được sắp xếp hiệu quả theo khu vực và sau đó theo nhánh vì các trang tính được đặt tên bằng cách sử dụng mẫu đó. Nếu bạn vẫn muốn sử dụng “Khoá Vùng”

tiếp cận và sắp xếp theo khu vực và sau đó phân nhánh, bạn có thể làm như vậy bằng cách điều chỉnh macro thêm một chút:

Sub SortWorksheets3()

Dim sTemp As String     Dim sSortArray(499) As String     Dim iReg As Integer     Dim I As Integer     Dim J As Integer     Dim K As Integer     Dim ws As Worksheet     Dim key As Worksheet

Application.ScreenUpdating = False

Set key = Worksheets("Region Key")



' Set tab colors and build sort array     J = 0     For Each ws in Worksheets         sTemp = UCase(ws.Name)

I = 2    ' Beginning row number         iReg = 0         While key.Cells(I, 1) > ""

If UCase(key.Cells(I, 1)) = sTemp Then iReg = key.Cells(I, 2)

I = I + 1         Wend

J = J + 1         sSortArray(J) = Right("00" & iReg, 2) & " " & ws.Name

Select Case iReg             Case 1                 ws.Tab.Color = vbRed             Case 2                 ws.Tab.Color = vbYellow             Case 3                 ws.Tab.Color = vbBlue             Case 4                 ws.Tab.Color = vbGreen             Case 5                 ws.Tab.Color = vbCyan             Case Else                 ws.Tab.ColorIndex = xlColorIndexNone                 ' Force into incorrect region area for sort                 sSortArray(J) = "00 " & ws.Name         End Select     Next ws

' Sort the worksheets     For I = 1 To Sheets.Count - 1         K = I         For J = I + 1 To Sheets.Count             If UCase(sSortArray(K)) > UCase(sSortArray(J)) Then K = J         Next J         If K <> I Then             Sheets(K).Move Before:=Sheets(I)

sTemp = sSortArray(K)

For J = K To I Step -1                 sSortArray(J) = sSortArray(J-1)

Next J             sSortArray(I) = sTemp         End If     Next I     Sheets("Region Key").Move Before:=Sheets(1)



Application.ScreenUpdating = True End Sub

Lưu ý rằng việc lặp lại macro này dựa vào mảng trợ giúp (sSortArray) để theo dõi cách sắp xếp các tên trong trang tính.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13710) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.