Solved

Sorting WS

Posted on 2014-04-05
13
267 Views
Last Modified: 2014-04-06
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".

I don't have much knowledge about VBA but I had written Code from different code. but it is not working. Please Help Me out on this.

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

Open in new window


Thanks
0
Comment
Question by:itjockey
  • 6
  • 5
  • 2
13 Comments
 
LVL 9

Expert Comment

by:Christopher Jay Wolff
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 

Open in new window


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

by:itjockey
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.

But Thanks For Reply.
0
 
LVL 9

Assisted Solution

by:Christopher Jay Wolff
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

Partial screen grab of link above.

and the added comments from people at the bottom are also helpful.
0
 
LVL 76

Expert Comment

by:GrahamSkan
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

Open in new window

0
 
LVL 76

Accepted Solution

by:
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

by:itjockey
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 8

Author Comment

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

Thanks
0
 
LVL 76

Assisted Solution

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

I misread the situation.
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

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
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

by:itjockey
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

by:itjockey
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

by:itjockey
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

by:GrahamSkan
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now