Sorting Sheets using VBA

[bizlife]

Hi,

I have a workbook with many sheets. The sheets are named as Purchase - Fund A, Purchase - Fund B...Sale - Fund A, Sale - Fund B and so on. When I am arranging the sheets, All Purchase sheets are coming first then all the sale sheets i.e. the sheets are arranged alphabetically. I want sheets to be arranged as: Purchase - Fund A, Sale - Fund A, Purchase - Fund B, Sale - Fund B.....
I am using the following code:

Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
  iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
  & "Clicking No will sort in Descending Order", _
  vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
  For i = 1 To Sheets.Count
  For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
  If iAnswer = vbYes Then
  If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
  Sheets(j).Move After:=Sheets(j + 1)
  End If
'
' If the answer is No, then sort in descending order.
'
  ElseIf iAnswer = vbNo Then
  If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
  Sheets(j).Move After:=Sheets(j + 1)
  End If
  End If
  Next j
  Next i
End Sub

Please help me on how to sort the sheets.

Thanks in advance.

 

[NARAYANK991]

Hi ,

Since your workbook is not available , I can only suggest a method.

1. Loop through the worksheet names ; concatenate the last letter of the tab name and the first letter of the tab name ; thus Purchase - Fund A will yield AP ; Sales - Fund A will yield AS.

2. Store each worksheet name along with this concatenated result in an unused area of a worksheet.

3. Sort this table alphabetically on the column containing the concatenated two letter text.

4. Use the code posted by Peter_SSs in the following link :

https://www.mrexcel.com/forum/excel-questions/583983-vba-sort-worksheets-same-order-list.html

Narayan