Re-Order Excel Worksheets by Name Topic is solved

[windowshopr]

I’m looking to re-order my worksheets in a workbook by name. I saw a VBA solution shown below, that I’d like to convert to AHK so I can use it in my script. Any ideas? Thanks!!

Code: Select all

 myOrder = Array("Sheet1", "Sheet4", "Sheet3", "Sheet6", "Sheet9", "Sheet11", "Sheet10", "Sheet5")

On Error Resume Next
  For x = UBound(myOrder) To LBound(myOrder) Step -1
    Worksheets(myOrder(x)).Move Before:=Worksheets(1)
  Next x
On Error GoTo 0

[boiler]

Here is a translation of the VBA code, which starts from the end of the list and moves each to the first position and steps backwards through them so they end up in the correct order:

Code: Select all

myOrder := ["Sheet1", "Sheet4", "Sheet3", "Sheet6", "Sheet9", "Sheet11", "Sheet10", "Sheet5"]
XL := ComObjActive("Excel.Application")
loop, % myOrder.Count()
	XL.Sheets(myOrder[myOrder.Count() - A_Index + 1]).Move(XL.Sheets(1))

Here's an alternate approach achieving the same thing, which moves each sheet to the one after the one appearing before it in the specified order:

Code: Select all

myOrder := ["Sheet1", "Sheet4", "Sheet3", "Sheet6", "Sheet9", "Sheet11", "Sheet10", "Sheet5"]
XL := ComObjActive("Excel.Application")
loop, % myOrder.Count() - 1
	XL.Sheets(myOrder[A_Index + 1]).Move(,XL.Sheets(myOrder[A_Index]))

If your workbook contains other sheets as well, the first script will move the sheets such that the first sheet will be in first position and others that are in the list after it (then the others not listed will appear at the end). In this scenario, the second script will leave the first sheet wherever it happens to be and move the rest of the ones in the list after it per the order. So if there were some other sheets before Sheet1 that aren't in the list, such as Sheet2 and Sheet7, they will remain before Sheet1 and the rest of the Sheets.

Both scripts assume you have a workbook already open with the sheets named per the myOrder list (in any order), and it will put them into the order shown in the list.

[windowshopr]

Wicked! That’s exactly what I was after. Thanks a lot!