Sorting sheets alphabetically--doesn't work on chartsheets

[cgmojoco]

Here is my code---but it will not sort the chartsheets! It only sorts the sheets that are normal, not built from the chart function. Please help thanks

Option Explicit

Sub SortWorksheets()

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

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then

'Change the 1 to the worksheet you want sorted first
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index <> .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

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 Before:=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M

End Sub

Thank you in advance

 

[cgmojoco]

Good morning kind sir-

YES! You have almost gotten me ALL the way.

Function Strip_Out(LongString As String, char As String)

Dim ResultString As String
For I = 1 To Len(LongString)

If Mid(LongString, I, 1) <> char Then ResultString = ResultString & Mid(LongString, I, 1)

Next I

Strip_Out = ResultString

End Function


Sub RenameSheets4()

Dim rng As Range
Dim Name_count As Integer
Dim I As Integer
Dim Suffix As String

Set rng = [g6]
Name_count = 0

Do Until rng = ""
Set rng = rng.Offset(13, 0)
Name_count = Name_count + 1
Loop


Sheets("Productivity").Move Before:=Sheets(Sheets.Count)

For I = 1 To Name_count

Select Case I
Case 1
Suffix = "-B"
Case 2
Suffix = "-F"
Case 3
Suffix = "-L"
Case Else
Suffix = ""
End Select


Sheets(I).Name = Strip_Out([g6].Offset((I - 1) * 13), ":") & Suffix

Next I


End Sub

Sub SortWorksheets()

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

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then

'Change the 1 to the worksheet you want sorted first
FirstWSToSort = 1
LastWSToSort = Sheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Productivity <> .Item(N).Productivity - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending Then
If UCase(Sheets(N).Name) > UCase(Sheets(M).Name) Then
Sheets(N).Move Before:=Sheets(M)
End If
Else
If UCase(Sheets(N).Name) < UCase(Sheets(M).Name) Then
Sheets(N).Move Before:=Sheets(M)
End If
End If
Next N
Next M

End Sub


The code renames the first 3 sheets with the proper suffix (YAH!)
However it is not carrying over the cell value for for the first three sheets as well, instead it carries the cell value from the next cell in the list. We need to set it up so that it carries the cell value of the first cell through the first three sheets.

In addition, we may need to throw a loop into the suffix piece because after the first three sheets the suffix is not being added.

Here is the file I am using
http://cgmojo.com/Misc/Test_005.zip

WE ARE SO CLOSE

 

[Glove_Man]

I am busy at the moment.

Just checking:

First three sheets are named [g6] follwed by suffix.

Then three sheets named [g19] followed by suffix? or does it skip to the fourth one?

I'll have a look if you explain very careful wht the names are supposed to be!

But notfor about 2hrs - deadline.

See you

 

[cgmojoco]

Yes thank you---you have it right!

To further clarify I'll illustrate an example of what I had in mind:

The value in cell G6, from the productivity sheet, is a username. Say it's USER: AANGELES

There are three pre-existing chart sheets in the workbook for each username.

I will make sure that there are a corresponding correct # of chart sheets for each username before the code is executed.


The first three charts will be re-named---whatever value is in G6 (stripping the : and stripping the word USERNAME from the value in the cell) followed by the suffix -B, -F, -L.

So the first three chart sheets will need to be renamed to AANGELES-B, AANGELES-F, and AANGELES-L respectively.

The next three chart sheets will be renamed whatever value is in G19 again with the suffixes and stripping.

This will be done for all usernames in the productivity sheet. The usernames can be found on the productivity sheet starting with cell G6 and every thirteenth cell thereafter.

I'm then going to have the sheets sorted alphabetically, keeping the Productivity sheet as the first sheet.

Thank you VERY much. My initial deadline has already passed; fortunately my superior has extended the deadline given that there is at least some functionality built into this modeling tool and he is able to use it albeit he is having some trouble. He will be very impressed if I can pull this off----if you need a job here in the USA let me know! :wink: (I can't thank you enough)

 

[Glove_Man]

Here you go:

I changed the sorting prcoedure a little - It puts Productivity last and then moves it back to the front as the very last step.

Theres also a redundant IF to make sure you don't accidentally change its name (like I did once).

Thanks for the job offer, but I'm very happy here. Its a bit far to commute to the US even the close side is 15 hours. Unless you're in Hawaii of course.

I have been to visit, but that's enough for me!

Philip

Sub RenameSheets5()

Dim rng As Range
Dim Name_count As Integer
Dim I As Integer
Dim Suffix As String

Set rng = Sheets("Productivity").[g6]
Name_count = 0

Do Until rng = ""
Set rng = rng.Offset(13, 0)
Name_count = Name_count + 1
Loop


Sheets("Productivity").Move after:=Sheets(Sheets.Count)

For I = 1 To Name_count
    For j = 1 To 3
        Select Case j
            
            Case 1
            Suffix = "-B"
            Case 2
            Suffix = "-F"
            Case 3
            Suffix = "-L"
                    
        End Select
        
    

    If Sheets((I - 1) * 3 + j).Name <> "Productivity" Then
    Sheets((I - 1) * 3 + j).Name = Right([g6].Offset((I - 1) * 13), Len([g6].Offset((I - 1) * 13)) - 6) & Suffix
    End If

    Next j
Next I


End Sub

Sub SortWorksheets()

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

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then

'Change the 1 to the worksheet you want sorted first
FirstWSToSort = 1
LastWSToSort = Sheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Productivity <> .Item(N).Productivity - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending Then
If UCase(Sheets(N).Name) > UCase(Sheets(M).Name) Then
Sheets(N).Move before:=Sheets(M)
End If
Else
If UCase(Sheets(N).Name) < UCase(Sheets(M).Name) Then
Sheets(N).Move before:=Sheets(M)
End If
End If
Next N
Next M

Sheets("Productivity").Move before:=Sheets(1)

End Sub

 

[cgmojoco]

Great THANK YOU SO MUCH AGAIN

This did the trick

Wow!