PDA

View Full Version : [SOLVED] Ordering worksheet tabs in a specific order



emu165
05-08-2020, 10:20 AM
Hi - First time poster. Please let me know if I am doing something incorrectly.
I have a file that will have a varying number of tabs. There will be Summary tabs and Detail tabs. I need to put the summary tab next to the appropriate Detail tab. Summary tabs will always start with Sum_(and then a tabname also will vary). The Detail tabs will start with Detail_(and then the tabname). So, the tabs will come in like this: SUM_1, SUM_X, Sum_Oth, Detail_1, Detail_X, Detail_Oth.
I need it to look like this: Sum_1, Detail_1, Sum_X, Detail_X, Sum_Oth, Detail_Oth.

Paul_Hossler
05-08-2020, 11:16 AM
Option Explicit


Sub SortTheTabs()
Dim i As Long, j As Long, o As Long, n As Long
Dim arySum() As String, sHold As String, sDetail As String
Dim ws As Worksheet

ReDim arySum(1 To ThisWorkbook.Worksheets.Count / 2)


i = 1
For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 3) = "Sum" Then
arySum(i) = ws.Name
i = i + 1
End If
Next

For i = 1 To UBound(arySum) - 1
For j = i + 1 To UBound(arySum)
If arySum(i) > arySum(j) Then
sHold = arySum(i)
arySum(i) = arySum(j)
arySum(j) = sHold
End If
Next j
Next i


o = 1
For i = 1 To UBound(arySum)

sDetail = "Detail_" & Right(arySum(i), Len(arySum(i)) - 4)
Worksheets(arySum(i)).Move before:=Worksheets(o)
Worksheets(sDetail).Move after:=Worksheets(arySum(i))

o = o + 2
Next i


End Sub

emu165
05-08-2020, 12:01 PM
Thanks Paul -- the more I see the more i need to learn. I did get a subscript out of range error on the following line
ReDim arySum(1 To ThisWorkbook.Worksheets.Count / 2)

Paul_Hossler
05-08-2020, 12:32 PM
Based on your example, I assumed that there was an even number of worksheet, each "Sum_" matched with a "Detail_", and there were no other sheets

If not true, then I can adjust

emu165
05-08-2020, 12:35 PM
There should be an even number. It may not always be what I provided in File1_Before.xlsx, but for every Sum_ there will be a Detail_. I tried to run it using the FILE1_BEFORE.XLSX file.

Paul_Hossler
05-08-2020, 03:45 PM
I used your original Before file in #1 and added my macro

emu165
05-12-2020, 04:43 PM
Thanks Paul...Not sure what I did but I so appreciate your help!