Koen tiene un libro de trabajo que contiene 150 hojas de trabajo, una para cada rama de la empresa. Necesita ordenar esas hojas según la región a la que pertenece cada rama. (Hay cinco regiones en su empresa). También señala que sería genial si el color de la pestaña de cada hoja de trabajo pudiera reflejar la región, y se pregunta si tal clasificación es posible en Excel.

Sí, esta clasificación es posible, pero requiere el uso de una macro.

La parte más difícil de crear la macro es definir cómo se determina qué ramas están en qué región. Quizás la forma más sencilla de hacer esto es asegurarse de que sus hojas de trabajo usen un patrón de nomenclatura que incluya tanto la región como la información de la rama. Por ejemplo, podría nombrar las hojas de trabajo con algo como «Reg01-Branch123». Su macro podría luego recorrer cada hoja de trabajo y hacer tanto el color como la clasificación.

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

La macro funciona con la colección Worksheets dos veces. En la primera pasada, el número de región se extrae del nombre de la hoja de trabajo.

Luego se usa (en la estructura Seleccionar caso) para establecer el color de la pestaña.

Si el número de región está fuera del rango de 1 a 5, no se realiza ningún cambio en el color de la pestaña.

Si su determinación de qué rama está en qué región es más compleja, deberá ajustar la macro en consecuencia. Por ejemplo, puede tener una hoja de trabajo llamada «Clave de región» que contiene, en la columna A, los nombres de cada una de sus sucursales y, en la columna B, el número de región correspondiente para cada una de esas sucursales. Suponiendo que los datos reales comienzan en la fila 2, podría modificar la macro de la siguiente manera:

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

La mayor diferencia entre esta macro y la anterior es que esta toma el número de región de una hoja de trabajo. Como última acción de la macro, la hoja de trabajo «Clave de región» se mueve al principio de la colección de Hojas de trabajo.

Tenga en cuenta que esta segunda macro también puede hacer que las pestañas de la hoja de trabajo, en la parte inferior de la ventana del programa, parezcan un caleidoscopio de colores.

La razón de esto es que las pestañas se ordenan según sus nombres, no según sus colores. Esto difiere de la primera macro, que ordenó efectivamente por región y luego por rama porque las hojas de trabajo se nombraron usando ese patrón. Si aún desea utilizar la «Clave de región»

acercarse y ordenar por región y luego ramificarse, puede hacerlo ajustando un poco más la macro:

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

Tenga en cuenta que esta iteración de la macro se basa en una matriz auxiliar (sSortArray) para realizar un seguimiento de cómo se deben ordenar los nombres en las hojas de trabajo.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13710) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.