Koen hat eine Arbeitsmappe mit 150 Arbeitsblättern, eines für jede Unternehmensfiliale. Er muss diese Blätter nach der Region sortieren, zu der jeder Zweig gehört. (In seinem Unternehmen gibt es fünf Regionen.) Er merkt außerdem an, dass es großartig wäre, wenn die Registerkartenfarbe für jedes Arbeitsblatt die Region widerspiegeln könnte, und fragt sich, ob eine solche Sortierung in Excel möglich ist.

Ja, eine solche Sortierung ist möglich, erfordert jedoch die Verwendung eines Makros.

Der schwierigste Teil beim Erstellen des Makros besteht darin, zu definieren, wie Sie bestimmen, welche Zweige sich in welcher Region befinden. Der einfachste Weg, dies zu tun, besteht darin, sicherzustellen, dass Ihre Arbeitsblätter ein Namensmuster verwenden, das sowohl die Regions- als auch die Zweiginformationen enthält. Beispielsweise könnten Sie die Arbeitsblätter so etwas wie „Reg01-Branch123“ nennen. Ihr Makro könnte dann durch jedes Arbeitsblatt gehen und sowohl die Färbung als auch die Sortierung durchführen.

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

Das Makro durchläuft die Worksheets-Auflistung zweimal. Beim ersten Durchgang wird die Regionsnummer aus dem Namen des Arbeitsblatts extrahiert.

Dies wird dann (in der Struktur „Fall auswählen“) verwendet, um die Registerkartenfarbe festzulegen.

Wenn die Regionsnummer außerhalb des Bereichs von 1 bis 5 liegt, wird die Farbe der Registerkarte nicht geändert.

Wenn Ihre Bestimmung, welcher Zweig in welcher Region liegt, komplexer ist, müssen Sie das Makro entsprechend anpassen. Beispielsweise haben Sie möglicherweise ein Arbeitsblatt mit dem Namen „Regionsschlüssel“, das in Spalte A die Namen der einzelnen Zweige und in Spalte B die entsprechende Regionsnummer für jeden dieser Zweige enthält. Angenommen, die tatsächlichen Daten beginnen in Zeile 2, können Sie das Makro folgendermaßen ändern:

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

Der größte Unterschied zwischen diesem und dem vorherigen Makro besteht darin, dass dieses die Regionsnummer aus einem Arbeitsblatt abruft. Als letzte Aktion im Makro wird das Arbeitsblatt „Regionsschlüssel“ an den Anfang der Arbeitsblattsammlung verschoben.

Beachten Sie, dass dieses zweite Makro auch dazu führen kann, dass Ihre Arbeitsblattregisterkarten am unteren Rand des Programmfensters wie ein Kaleidoskop von Farben aussehen.

Der Grund dafür ist, dass die Registerkarten nach ihren Namen und nicht nach ihren Farben sortiert sind. Dies unterscheidet sich vom ersten Makro, das effektiv nach Region und dann nach Zweig sortiert wurde, da die Arbeitsblätter nach diesem Muster benannt wurden. Wenn Sie weiterhin den „Regionsschlüssel“ verwenden möchten

Wenn Sie sich nähern und nach Region sortieren und dann verzweigen, können Sie dies tun, indem Sie das Makro etwas weiter anpassen:

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

Beachten Sie, dass diese Iteration des Makros auf einem Hilfsarray (sSortArray) basiert, um zu verfolgen, wie die Namen in den Arbeitsblättern sortiert werden sollen.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13710) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.