Solved

# Sorting WS

Posted on 2014-04-05
271 Views
Hi Experts,

How Do I Sort Worksheets which is between worksheet name Start to Worksheet Name Finish. there are 10 worksheets between Sheet "Start" To Sheet "Finish".

``````Sub Sort()
Dim S As Integer, SS As Integer, Start As Integer, Finish As Integer
Start = Worksheets("Start").Index
Finish = Worksheets("Finish").Index

If (Finish - Start) > 2 Then
For SS = Start + 1 To Finish - 2
For S = Start + 1 To Finissh - 2
If Worksheets(S + 1).Name < Worksheets(S).Name Then Worksheets(S + 1).Move before:=Worksheets(S)
Next
Next
End If
End Sub
``````

Thanks
0
Question by:Naresh Patel
[X]
###### 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
• 6
• 5
• 2

LVL 9

Expert Comment

ID: 39980050
Hi.  I'm trying to help since no one has answered yet, but I cannot run vb on my system and I'm new with vb, and only hope my suggestion below helps you.  I am not sure you want the SS For loop as I don't know if you're sub-sorting, or if you do need the SS loop.  To help my brain think, I added the variable WSQuantity.

``````Sub Sort()
Dim S, SS, Start, Finish, WSQuantity as Integer
Start = Worksheets("Start").Index
Finish = Worksheets("Finish").Index
WSQuantity=(Finish-Start)+1

If (WSQuantity) > 1 Then
For S = Start To (Finish-1)
If Worksheets(S + 1).Name < Worksheets(S).Name Then Worksheets(S + 1).Move before:=Worksheets(S)
Next
End If
End Sub
``````

and here are some good examples of sorting worksheets...
http://www.vbaexpress.com/kb/getarticle.php?kb_id=72

and...
http://stackoverflow.com/questions/5435499/sorting-worksheets-in-excel-with-vba-using-names-as-dates
0

LVL 8

Author Comment

ID: 39980075
I already visited Vbaexperess. but thing is that I want to sort the sheets which is between sheet Start - Finish.

And I had run your code I guess it have some flaws.

0

LVL 9

Assisted Solution

Christopher Jay Wolff earned 50 total points
ID: 39980141
Your welcome.  I bet it did.  I guess you'd have to change the math back to the way you had it, in order to go in between.

I just found out you have to use "Set" when assigning values to objects as referred to here
http://stackoverflow.com/questions/3872339/what-is-the-difference-between-dim-and-set-in-vba

0

LVL 76

Expert Comment

ID: 39980183
@itjockey,
If you get an error, please say what the error is.

This will sort the sheets by name, except for the first and the last sheet.
``````Option Explicit

Sub SortSheets()
Dim i As Integer
Dim j As Integer
Dim wbk As Workbook
Dim bMoved As Boolean

Set wbk = ActiveWorkbook

Do
bMoved = False
For i = 2 To wbk.Sheets.Count - 1
DoEvents
For j = i + 1 To wbk.Sheets.Count - 1
If wbk.Sheets(i).Name > wbk.Sheets(j).Name Then
wbk.Sheets(i).Move After:=wbk.Sheets(j)
DoEvents
bMoved = True
Exit For
End If
Next j
If bMoved Then
Exit For
End If
Next i
Loop While bMoved
End Sub
``````
0

LVL 76

Accepted Solution

GrahamSkan earned 450 total points
ID: 39980213
Try to ensure that you always have 'Option Explicit' at the top of every code module.

This would have warned you with a 'Variable not defined' error that in line 8 because 'Finish' was misspelled as 'Finish'.

This is controlled by an option in the VBA IDE (Tools>Options>Editor tab).

It is labelled 'Require Variable Declaration' and I recommend that it be ticked. While you are there you might like to remove the tick from the 'Auto Syntax Check' checkbox. This will prevent the annoying message that comes up when you leave a line unfinished (e.g. to copy a name or some code from elsewhere).
0

LVL 8

Author Comment

ID: 39980268
@GrahamSkan,
I have 20 sheets and I want to sort A -Z sheets which is between sheet "Start" To Sheet "Finish". Between these two sheets there is only 5 sheets. Sort only these 5 Sheets.
code you mention. Sort the whole sheets.

Thanks
0

LVL 8

Author Comment

ID: 39980271
Defiantly I will check after spell properly. Traveling now.

Thanks
0

LVL 76

Assisted Solution

GrahamSkan earned 450 total points
ID: 39980478
OK, Just remember that Option Explicit will do that job for you. Do use it.

Remember that we are here to educate, not to do your job for you. This is a relatively simple request, so the code below shows how to take the names of the delimiting sheets into account.
``````Option Explicit

Sub SortSheets()
Dim i As Integer
Dim j As Integer
Dim wbk As Workbook
Dim bMoved As Boolean

Set wbk = ActiveWorkbook
Dim iStart As Integer
Dim iEnd As Integer
iStart = wbk.Sheets("Start").Index
iEnd = wbk.Sheets("Finish").Index

Do
bMoved = False
For i = iStart + 1 To iEnd - 1
DoEvents
For j = i + 1 To iEnd - 1
If wbk.Sheets(i).Name > wbk.Sheets(j).Name Then
wbk.Sheets(i).Move After:=wbk.Sheets(j)
DoEvents
bMoved = True
Exit For
End If
Next j
If bMoved Then
Exit For
End If
Next i
Loop While bMoved
End Sub
``````
0

LVL 76

Expert Comment

ID: 39980500
;>)
English may not be your first language. Please check the translation of the first word of your comment #39980271: "Defiantly".

I think that you meant "Definitely".  :>)
0

LVL 8

Author Comment

ID: 39980821
Yes English is not my First language.And that comment posted via cell phone so auto correction did that.

Thanks
0

LVL 8

Author Closing Comment

ID: 39980860
Mr.GrahamSkan,

This would have warned you with a 'Variable not defined' error that in line 8 because 'Finish' was misspelled as 'Finish'.
How dumb i am.....i had change to "Finish" & code working fine. so my question is to you - you have provided 32 line of code & the one i posed is 13 line.
which one is more appropriate?

Thanks
0

LVL 8

Author Comment

ID: 39980863
i had done my most of education in my native language so find difficulties to frame sentence in English. so ignorantly i miss type word or sentence ....apology.
0

LVL 76

Expert Comment

ID: 39980946
I wrote the code from scratch, because I found that sometimes simple code such as yours didn't always work. It seemed that after disturbing the order with one move, the code was losing its place.
My code starts the whole thing again after each move. There wouldn't be an enormous number of sheets, so any extra time wouldn't be noticeable. However, if your code works OK for you, then use it by all means.
0

## Featured Post

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with â€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
###### Suggested Courses
Course of the Month8 days, 15 hours left to enroll