Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting worksheets alphabetically (code)
Hi
I found some code on the Internet that is *supposed* to sort my worksheets alphabetically. I created a command button and entered the following code into it: Private Sub CommandButton1_Click() 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 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 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 End Sub Anyway, whenever I click the command button to do the sorting, I get the following error: "Compile error: Expected end sub" The following is highlighted in the code window: Private Sub CommandButton1_Click(). Any idea what I'm doing wrong here? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting worksheets alphabetically (code)
I copied your code to a button and came up with a different error so I'm
assuming the code wasn't posted fully. Anyway to answer your question. Take out the Sub SortWorksheets() and one of the ending End Subs. HTH "Ray Ash" wrote in message ... Hi I found some code on the Internet that is *supposed* to sort my worksheets alphabetically. I created a command button and entered the following code into it: Private Sub CommandButton1_Click() 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 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 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 End Sub Anyway, whenever I click the command button to do the sorting, I get the following error: "Compile error: Expected end sub" The following is highlighted in the code window: Private Sub CommandButton1_Click(). Any idea what I'm doing wrong here? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting worksheets alphabetically (code)
Private Sub You Rock!()
It worked! You rock! Thanks for the help! End Sub -----Original Message----- I copied your code to a button and came up with a different error so I'm assuming the code wasn't posted fully. Anyway to answer your question. Take out the Sub SortWorksheets() and one of the ending End Subs. HTH "Ray Ash" wrote in message ... Hi I found some code on the Internet that is *supposed* to sort my worksheets alphabetically. I created a command button and entered the following code into it: Private Sub CommandButton1_Click() 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 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 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 End Sub Anyway, whenever I click the command button to do the sorting, I get the following error: "Compile error: Expected end sub" The following is highlighted in the code window: Private Sub CommandButton1_Click(). Any idea what I'm doing wrong here? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Alphabetically | Excel Worksheet Functions | |||
sorting using letters , but not alphabetically. | Excel Worksheet Functions | |||
Sorting columns alphabetically | Excel Worksheet Functions | |||
Sorting Alphabetically | Excel Discussion (Misc queries) | |||
Sorting data in a cell alphabetically | Excel Worksheet Functions |