Re-arrange excel sheet order

Hi everyone, i have an excel file with many sheets. They are not the correct order i want them to be, for example.
image
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 :heart:

1 Like

@KSK_R_D

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,

image
i have a little problem here, i followed the instruction but it didn’t work

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

i did but it’s having the same error

Hi @KSK_R_D

Please check the method name and the text file path were given correctly or not

Regards

1 Like

Hi @KSK_R_D

Thank You.

Happy Automation!!

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

HI @KSK_R_D

It will work for different names also.

Regards

1 Like

oh silly me! thanks a lot sir!!!

Hi @KSK_R_D

Anytime sir!!

Happy Automation!!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.