Sorting sheets alphabetically--doesn't work on chartsheets

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
I think you will need to use a Sheet( whatever ) instead of Worksheets( whatever ) since the Chart objects are Sheets but not Worksheets.
 

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
Thanks but how would this look?

Do I just replace sheet wherever the word worksheet is?

Regards-
 

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696

ADVERTISEMENT

Glove_Man said:
I think you will need to use a Sheet( whatever ) instead of Worksheets( whatever ) since the Chart objects are Sheets but not Worksheets.

I tried replacing the worksheet word with Chart but it did not work---- :unsure:
 

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
I did a little checking and I'm pretty sure that just using Sheet and Sheets in place of Worksheet and Worksheets will work. I did some testing and a .select worked when using Sheet but not when using worksheet. Which seems exactly your problem.
 

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696

ADVERTISEMENT

Will you post the new code then--I am not getting it.

Much appreciated....
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
I tried replacing the worksheet word with Chart but it did not work
How did it not work?

Did it cause errors or not to the sort as expected?

The following worked for me when I did a small test.
Code:
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).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 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
 

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
Ok---I see....yes works great! Thank you!

Would I do the same to this renaming code? If so would you mind posting the adjusted code. I am new to VB and under a time crunch for this project....


Sub RenameSheets()
Sheets("Index").Select
ActiveSheet.Move after:=Worksheets(Worksheets.Count)
Dim Rng As Range, c As Range, i As Integer, Sh As Worksheet
Set Sh = Sheets("Index") 'sheet where list is located
Set Rng = Sh.Range("A1:A" & Range("A65536").End(xlUp).Row) '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

Thanks in advance-
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
I'm not sure what the code is supposed to do exactly but does the following work:
Code:
Sub RenameSheets()

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

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

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

    Set Rng = Sh.Range("A1:A" & Range("A65536").End(xlUp).Row) '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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top