Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel VBA sort worksheets by name

Posted on 2014-09-29
6
310 Views
Last Modified: 2014-09-29
I got this below code from Microsoft, the problem is that it works to some extend but when i have the 15 Sheets in a workbook where their names  are like  
Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6
Sheet7
Sheet8
Sheet9
Sheet10
Sheet11
Sheet12
Sheet13
Sheet14
Sheet15

once i run this code  after sheet1 the sheet10 gets located. where it should be Sheet2 Not Sheet10

any idea?

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

Open in new window

0
Comment
Question by:ProfessorJimJam
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40349457
Hi,

Your code sort in alphabetical order

if you want to sort sheets with name Like "Sheet" + Number

pls try

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 Mid(Sheets(j).Name, 6) * 1 > Mid(Sheets(j + 1).Name, 6) * 1 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 Mid(Sheets(j).Name, 6) * 1 < Mid(Sheets(j + 1).Name, 6) * 1 Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub 

Open in new window

Regards
0
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40349466
thanks. but problem is that i used Sheet# as an example.  the sheet names does not start always with equal alphabetic character.   for example if it is Sunday1 Sunday2 Saturday1 Saturday2 and so on.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40349552
pls try

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.
'
        ShNameWithoutNr = fStringWithoutEndingNumber(Sheets(j).Name)
        ShPlus1NameWithoutNr = fStringWithoutEndingNumber(Sheets(j + 1).Name)
        ShNameEndNr = fEndingNumber(Sheets(j).Name)
        ShPlus1NameEndNr = fEndingNumber(Sheets(j + 1).Name)
         
         If iAnswer = vbYes Then

            If ShNameWithoutNr > ShPlus1NameWithoutNr Or _
               ShNameWithoutNr = ShPlus1NameWithoutNr And ShNameEndNr > ShPlus1NameEndNr 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 ShNameWithoutNr < ShPlus1NameWithoutNr Or _
               ShNameWithoutNr = ShPlus1NameWithoutNr And ShNameEndNr < ShPlus1NameEndNr Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub

Function fStringWithoutEndingNumber(myStr As String) As String
For Idx = Len(myStr) To 1 Step -1
    If Mid(myStr, Idx, 1) Like "[0-9]" And FoundLetter <> True Then
        tmp = ""
    Else
        tmp = Mid(myStr, Idx, 1)
        FoundLetter = True
    End If
    Res = tmp & Res
Next
fStringWithoutEndingNumber = Res
End Function

Function fEndingNumber(myStr As String) As String
For Idx = Len(myStr) To 1 Step -1
    If Mid(myStr, Idx, 1) Like "[0-9]" And FoundLetter <> True Then
        tmp = Mid(myStr, Idx, 1)
    Else
        tmp = ""
        FoundLetter = True
    End If
    Res = tmp & Res
Next
fEndingNumber = Res
End Function

Open in new window

0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40349561
i tried this latest one but still Sheet10 and Sheet11 comes after Sheet1
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40349576
Small correction

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.
'
        ShNameWithoutNr = fStringWithoutEndingNumber(Sheets(j).Name)
        ShPlus1NameWithoutNr = fStringWithoutEndingNumber(Sheets(j + 1).Name)
        ShNameEndNr = fEndingNumber(Sheets(j).Name)
        ShPlus1NameEndNr = fEndingNumber(Sheets(j + 1).Name)
         
         If iAnswer = vbYes Then

            If ShNameWithoutNr > ShPlus1NameWithoutNr Or _
               ShNameWithoutNr = ShPlus1NameWithoutNr And ShNameEndNr * 1 > ShPlus1NameEndNr * 1 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 ShNameWithoutNr < ShPlus1NameWithoutNr Or _
               ShNameWithoutNr = ShPlus1NameWithoutNr And ShNameEndNr * 1 < ShPlus1NameEndNr * 1 Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub

Function fStringWithoutEndingNumber(myStr As String) As String
For Idx = Len(myStr) To 1 Step -1
    If Mid(myStr, Idx, 1) Like "[0-9]" And FoundLetter <> True Then
        tmp = ""
    Else
        tmp = Mid(myStr, Idx, 1)
        FoundLetter = True
    End If
    Res = tmp & Res
Next
fStringWithoutEndingNumber = Res
End Function

Function fEndingNumber(myStr As String) As String
For Idx = Len(myStr) To 1 Step -1
    If Mid(myStr, Idx, 1) Like "[0-9]" And FoundLetter <> True Then
        tmp = Mid(myStr, Idx, 1)
    Else
        tmp = ""
        FoundLetter = True
    End If
    Res = tmp & Res
Next
fEndingNumber = Res
End Function

Open in new window

Regards
0
 
LVL 26

Author Comment

by:ProfessorJimJam
ID: 40349580
No Wonder, how you got the title of Genius. you are indeed Genius :-)

thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question