Mensa Genius question: sorting worksheets by name
 

I will be SHOCKED if anyone can answer this question:

There are 20 worksheets, and more keep being added. After every addition, I
need to have VB move the sheets around so that they are in alphabetical
order, from left to right.

Paul
go ahead, impress us :-)

Mensa Genius question: sorting worksheets by name
 

Try Chip Pearson's code:


Sub AlphaSortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False
FirstWSToSort = 1
LastWSToSort = Worksheets.Count

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move befo=Worksheets(M)
End If
End If
Next N
Next M

End Sub


--


Gary


"Paul" wrote in message
...
I will be SHOCKED if anyone can answer this question:

There are 20 worksheets, and more keep being added. After every addition, I
need to have VB move the sheets around so that they are in alphabetical
order, from left to right.

Paul
go ahead, impress us :-)

Mensa Genius question: sorting worksheets by name
 

press alt-f11 to invoke Visual Basic in Excel
then right-click ThisWorkbook and select "View Code"
Then paste the following code the
--------------------------------------------
Private Sub Workbook_NewSheet(ByVal sh As Object)
sortSheets ThisWorkbook
End Sub

Sub sortSheets(wb As Workbook)
Dim sh As Integer
If wb.Sheets.Count < 2 Then Exit Sub
For sh = 2 To wb.Sheets.Count
If wb.Sheets(sh - 1).Name wb.Sheets(sh).Name Then
wb.Sheets(sh).Move wb.Sheets(sh - 1)
sh = 2
End If
Next sh
End Sub


"Paul" wrote:

I will be SHOCKED if anyone can answer this question:

There are 20 worksheets, and more keep being added. After every addition, I
need to have VB move the sheets around so that they are in alphabetical
order, from left to right.

Paul
go ahead, impress us :-)

Mensa Genius question: sorting worksheets by name
 

You guys are KILLING me with your knowledge. I can't believe that people
actually know how to do this stuff. I truly am amazed.

Paul


"Paul" wrote:

I will be SHOCKED if anyone can answer this question:

There are 20 worksheets, and more keep being added. After every addition, I
need to have VB move the sheets around so that they are in alphabetical
order, from left to right.

Paul
go ahead, impress us :-)

Mensa Genius question: sorting worksheets by name
 

Hi Paul,

A Mensa Genius would have looked at your post and said, "There is no
question! You stated a need, not a question." <vbg

There are a quite a few Excel Mensa Genius' floating around out there, as
you observed.

Is Mensa spelled correctly, my spell checker said no, but did not offer
anything that looked proper. No chance of me not being able to spell Mensa
and "...now I are one!" <vbg

Regards,
Howard

"Paul" wrote in message
...
I will be SHOCKED if anyone can answer this question:

There are 20 worksheets, and more keep being added. After every addition,
I
need to have VB move the sheets around so that they are in alphabetical
order, from left to right.

Paul
go ahead, impress us :-)

Mensa Genius question: sorting worksheets by name
 

Yes Mensa is spelt correctly

Mensa Genius question: sorting worksheets by name
 

In article . com,
"geo@OZ" wrote:

Yes Mensa is spelt correctly


But what does wheat have to do with Mensa?