Koen에는 각 회사 지점에 대해 하나씩 150 개의 워크 시트가 포함 된 통합 문서가 있습니다. 그는 각 지점이 속한 지역을 기준으로 시트를 정렬해야합니다. (회사에는 5 개의 지역이 있습니다.) 또한 그는 각 워크 시트의 탭 색상이 지역을 반영 할 수 있으면 좋을 것이라고 언급하고 Excel에서 이러한 정렬이 가능한지 궁금합니다.

예, 이러한 정렬이 가능하지만 매크로를 사용해야합니다.

매크로를 만드는 가장 어려운 부분은 어떤 지점이 어떤 지역에 있는지 결정하는 방법을 정의하는 것입니다. 이를 수행하는 가장 간단한 방법은 워크 시트가 지역 및 지점 정보를 모두 포함하는 이름 지정 패턴을 사용하는지 확인하는 것입니다. 예를 들어 워크 시트의 이름을 “Reg01-Branch123″과 같이 지정할 수 있습니다. 그런 다음 매크로가 각 워크 시트를 단계별로 수행하고 색상 지정과 정렬을 모두 수행 할 수 있습니다.

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

매크로는 Worksheets 컬렉션을 통해 두 번 작동합니다. 첫 번째 통과시 워크 시트 이름에서 지역 번호가 추출됩니다.

그런 다음 (케이스 선택 구조에서) 탭 색상을 설정하는 데 사용됩니다.

영역 번호가 1-5 범위를 벗어나면 탭 색상이 변경되지 않습니다.

어떤 지역에 어떤 분기가 있는지 결정하는 것이 더 복잡한 경우 그에 따라 매크로를 조정해야합니다. 예를 들어, A 열에 각 지점의 이름과 B 열에 각 지점에 해당하는 지역 번호가 포함 된 “Region Key”라는 워크 시트가있을 수 있습니다. 실제 데이터가 행 2에서 시작한다고 가정하면 다음과 같은 방식으로 매크로를 수정할 수 있습니다.

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

이 매크로와 이전 매크로의 가장 큰 차이점은 워크 시트에서 지역 번호를 가져 오는 것입니다. 매크로의 마지막 동작으로 “Region Key”워크 시트가 Worksheets 컬렉션의 맨 처음으로 이동합니다.

이 두 번째 매크로는 프로그램 창 하단에 컬러 만화경처럼 보이는 워크 시트 탭을 생성 할 수도 있습니다.

그 이유는 탭이 색상이 아닌 이름을 기준으로 정렬되기 때문입니다. 이 패턴을 사용하여 워크 시트의 이름이 지정 되었기 때문에 지역별로 그리고 분기별로 효과적으로 정렬 된 첫 번째 매크로와 다릅니다. “지역 키”를 계속 사용하려면

지역별로 접근하고 정렬 한 다음 분기하면 매크로를 조금 더 조정하면됩니다.

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

이 매크로 반복은 워크 시트의 이름이 정렬되는 방식을 추적하기 위해 도우미 배열 (sSortArray)에 의존합니다.

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (13710)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.