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]

Thank you- that did exactly what I asked for.

This script was meant to rename the sheets based on a list.

 

[cgmojoco]

Gosh I must be getting tired

With this code from the renaming script:
Set Rng = Sh.Range("A1:A" & Range("A65536").End(xlUp).Row)

I can rename all sheets based on values from the A column, but I have changed my mind and don't want to use these values.

Instead how would I change the range to be cells:
G6
G19
G32
G45
G58

Thank you for your time!

 

[Glove_Man]

Try

Set rng = Union(sh.range(g6),sh.range(g19),sh.range(g32), sh.range(g45),sh.range(g58))

That selects a non continuous range of cells. Then selection.cells(i) can be used to call out values.

BTW, I'm just curious but which country are you in?

 

[cgmojoco]

Try

Set rng = Union(sh.range(g6),sh.range(g19),sh.range(g32), sh.range(g45),sh.range(g58))

That selects a non continuous range of cells. Then selection.cells(i) can be used to call out values.

BTW, I'm just curious but which country are you in?

Thank you----so in this scenario----

Sub RenameSheets()

Dim Rng As Range
Dim c As Range
Dim I As Integer
Dim Sh As Worksheet

Set Sh = Sheets("Productivity") 'sheet where list is located

Sh.Move After:=Sheets(Sheets.Count)

Set Rng = Union(Sh.Range(g6), Sh.Range(g19), Sh.Range(g32), Sh.Range(g45), Sh.Range(g58)) 'replace this with your range of names

If Rng.Cells.Count >= Sheets.Count Then
MsgBox "You have entered too many sheet names!!"
Exit Sub
End If

For Each c In Rng
I = I + 1
On Error Resume Next
Sheets(I).Name = c

If Error <> 0 And Sheets(I).Name <> Sh.Name Then 'ignore sheet named index
Sheets(CStr(c)).Name = Format(Time, "HHMMSSSS") & "-" & I 'temporarily rename sheet
Sheets(I).Name = c
End If
Error 0

Next c

End Sub

How would I rewrite to call the values out in the proper place? I'm getting runtime error '1004'; with your code that I attempted to splice into the original code.

FYI I am from the USA. Thank you both for all of your help.....

 

[Glove_Man]

You can do that, but it sets the number of sheets at a constant 5 if you do..... The original code allowed for any nummber of sheets.

If you're going to make it a constant five sheets you may as well name them directly.

Sub RenameSheets2()

If Sheets.Count <> 6 Then
MsgBox "You don't have the right number of sheets"
Exit Sub
End If

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

Sheets(1).Name = [g6]
Sheets(2).Name = [g19]
Sheets(3).Name = [g32]
Sheets(4).Name = [g45]
Sheets(5).Name = [g58]

End Sub

But as i said that has destroyed much of the functionality. If you want to keep the unknown number of sheets then you'd better put all the names together in a list somehwere. Anywhere in the sheet that makes sense would do.

 

[Glove_Man]

If you always increment by 13 rows per item you could try the following:

Unfortunately I'm not much chop at error trapping so Icn't replicate that bit of code.

Sub RenameSheets3()

Dim rng As Range
Dim Name_count As Integer
Dim I As Integer

Set rng = [g6]
Name_count = 0

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

If Sheets.Count <> Name_count + 1 Then
MsgBox "You don't have the right number of sheets"
Exit Sub
End If

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

For I = 1 To Name_count
Sheets(I).Name = [g6].Offset((I - 1) * 13)
Next I


End Sub

 

[cgmojoco]

If you always increment by 13 rows per item you could try the following:

Unfortunately I'm not much chop at error trapping so Icn't replicate that bit of code.

Sub RenameSheets3()

Dim rng As Range
Dim Name_count As Integer
Dim I As Integer

Set rng = [g6]
Name_count = 0

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

If Sheets.Count <> Name_count + 1 Then
MsgBox "You don't have the right number of sheets"
Exit Sub
End If

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

For I = 1 To Name_count
Sheets(I).Name = [g6].Offset((I - 1) * 13)
Next I


End Sub

Great thanks----
I ended up taking out the error trapping so I ended up with this:

Sub RenameSheets3()

Dim rng As Range
Dim Name_count As Integer
Dim I As Integer

Set rng = [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
Sheets(I).Name = [g6].Offset((I - 1) * 13)
Next I


End Sub

While we are on a roll here two other questions----perhaps I should have asked these first

1st- Some of the cells have a : in them. How can I get the code to ignore the : I.e. what would be the code to remove the : from the same G cells I specified above--(yes they are every 13th row, good observation)?

2nd- How can I have the the sheets named in groups? I.e. I want to name the first three sheets whatever value is in the cell + the suffix -B, -F, and -L respectivley.

I'm sure this is to much to ask but if you do happen to know it would be really appreciated![/quote]

 

[Glove_Man]

Is the : at the start, end or in the middle?

 

[cgmojoco]

Is the : at the start, end or in the middle?

In the middle

Example-
USER: BGAINES

This is unfortunatley generated by an SQL import so I cannot change the format until it arrives in the Excel sheet.

Wow thank you very much for the follow up!

 

[Glove_Man]

Use a predefined function to handle removing the : such as

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

And then use this:

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 after:=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

I'm off home now - so no more questoins 'till tommorow! Good Luck!