Hi everyone, i have an excel file with many sheets. They are not the correct order i want them to be, for example.
I want to re-arrange them from S1-S5, automatically instead of clicking one by one because i have a lot more to process.
I would be appreciate if someone could help me with this!
Hi @KSK_R_D
You can use this Invoke VBA code:
New Text Document.txt (1.1 KB)
Workflow:
Input:
Output:
Regards
1 Like
Hi @KSK_R_D ,
You can try following the macro to sort the sheets
following code to sort tabs ascendingly
Sub SortTabs()
Dim iCount As Integer
Dim x, y, z As Integer
'count how many sheets in the workbook
iCount = ActiveWorkbook.Sheets.Count
'if only one sheet, exit the macro
If iCount = 1 Then Exit Sub
'otherwise sort alphabetically
For x = 1 To iCount - 1
For y = x + 1 To iCount
If Sheets(y).Name < Sheets(x).Name Then
Sheets(y).Move Before:=Sheets(x)
End If
Next y
Next x
End Sub
Descending alphabetical order
Sub SortTabsDesc()
Dim iCount As Integer
Dim x, y, z As Integer
'count how many sheets in the workbook
iCount = ActiveWorkbook.Sheets.Count
'if only one sheet, exit the macro
If iCount = 1 Then Exit Sub
'otherwise sort alphabetically descending
For x = 1 To iCount - 1
For y = x + 1 To iCount
'this is the line that changes for descending
If Sheets(y).Name > Sheets(x).Name Then
Sheets(y).Move Before:=Sheets(x)
End If
Next y
Next x
End Sub
Thanks, i’ll try it later 
1 Like
Sub ReorderSheets()
Dim i As Integer
Dim ws As Worksheet
' Loop through sheets and move them to the desired order
For i = 5 To 1 Step -1
Set ws = Sheets("S" & i)
ws.Move Before:=Sheets(1)
Next i
End Sub
Hi,
FYI, another approach using ClosedXML
Using wb As New ClosedXML.Excel.XLWorkbook(filename)
Dim sheets As ClosedXML.Excel.IXLWorksheets = wb.Worksheets
Dim arrSheetName As String() = sheets.Select(Function(s) s).ToArray()
Dim i As int32 =1
For Each s As String In arrSheetName
wb.Worksheet(s).Position=i
i=i+1
Next
wb.Save()
End Using
Regards,
Hi @KSK_R_D
Please enable the micro settings for you excel.
Once you go to the below mentioned view by clicking the file option in excel then you please follow the steps:
After going to macros please enable all those settings and then please run the code
Regards
Regards
1 Like
1 Like
Oh thanks, i did change the method name
1 Like
another question, what if i use random name for those sheets, i’ve just given S1, S2…S5 for example, actually, my sheets have a lot of different names
1 Like
oh silly me! thanks a lot sir!!!
1 Like
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
prashant1603765
gorby
sharazkm32
pabaleloh