| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting sheets in workbook
I have a workbook that has sheets in it that are sheet1 €“ sheet33. I tried to
use the code from http://cpearson.com/excel/sortws.htm The problem is that it does not handle the numbers in the sheet in the right order. It puts them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas? Thanks! Jeff |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting sheets in workbook
Jeff,
Try 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 CInt(Mid(Worksheets(N).Name, 6)) CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If CInt(Mid(Worksheets(N).Name, 6)) < CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a workbook that has sheets in it that are sheet1 - sheet33. I tried to use the code from http://cpearson.com/excel/sortws.htm The problem is that it does not handle the numbers in the sheet in the right order. It puts them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas? Thanks! Jeff |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting sheets in workbook
I just updated the web page to include this code.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Chip Pearson" wrote in message ... Jeff, Try 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 CInt(Mid(Worksheets(N).Name, 6)) CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If CInt(Mid(Worksheets(N).Name, 6)) < CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a workbook that has sheets in it that are sheet1 - sheet33. I tried to use the code from http://cpearson.com/excel/sortws.htm The problem is that it does not handle the numbers in the sheet in the right order. It puts them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas? Thanks! Jeff |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting sheets in workbook
That is excellent, you guys are amazing. Thanks! Jeff
"Chip Pearson" wrote: Jeff, Try 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 CInt(Mid(Worksheets(N).Name, 6)) CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If CInt(Mid(Worksheets(N).Name, 6)) < CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a workbook that has sheets in it that are sheet1 - sheet33. I tried to use the code from http://cpearson.com/excel/sortws.htm The problem is that it does not handle the numbers in the sheet in the right order. It puts them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas? Thanks! Jeff |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting sheets in workbook
I have a different workbook that the sheet are not named Sheet1 - Sheet 33
but it is named Red1 - Red15. The code errors out if they are not named Sheet. Any Ideas? Thanks! Jeff "Chip Pearson" wrote: Jeff, Try 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 CInt(Mid(Worksheets(N).Name, 6)) CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If CInt(Mid(Worksheets(N).Name, 6)) < CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a workbook that has sheets in it that are sheet1 - sheet33. I tried to use the code from http://cpearson.com/excel/sortws.htm The problem is that it does not handle the numbers in the sheet in the right order. It puts them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas? Thanks! Jeff |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting sheets in workbook
Change the
Mid(Worksheets(N).Name, 6) to Mid(Worksheets(N).Name, 4) in all four instances. The number in the Mid statement should be the character position of the first number in the worksheet name. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a different workbook that the sheet are not named Sheet1 - Sheet 33 but it is named Red1 - Red15. The code errors out if they are not named Sheet. Any Ideas? Thanks! Jeff "Chip Pearson" wrote: Jeff, Try 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 CInt(Mid(Worksheets(N).Name, 6)) CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If CInt(Mid(Worksheets(N).Name, 6)) < CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a workbook that has sheets in it that are sheet1 - sheet33. I tried to use the code from http://cpearson.com/excel/sortws.htm The problem is that it does not handle the numbers in the sheet in the right order. It puts them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas? Thanks! Jeff |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting sheets in workbook
Is there a way to have different length sheet names like Sheet1 - Sheet20 and
Red1 - Red15 in the same workbook and sort them all alphabetically first then numerically? Thanks! Jeff "Chip Pearson" wrote: Change the Mid(Worksheets(N).Name, 6) to Mid(Worksheets(N).Name, 4) in all four instances. The number in the Mid statement should be the character position of the first number in the worksheet name. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a different workbook that the sheet are not named Sheet1 - Sheet 33 but it is named Red1 - Red15. The code errors out if they are not named Sheet. Any Ideas? Thanks! Jeff "Chip Pearson" wrote: Jeff, Try 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 CInt(Mid(Worksheets(N).Name, 6)) CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If CInt(Mid(Worksheets(N).Name, 6)) < CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a workbook that has sheets in it that are sheet1 - sheet33. I tried to use the code from http://cpearson.com/excel/sortws.htm The problem is that it does not handle the numbers in the sheet in the right order. It puts them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas? Thanks! Jeff |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting sheets in workbook
My free Excel add-in "Excel Extras" will do that as well as
other stuff including inserting a table of contents and providing several text case options. Download here... http://www.realezsites.com/bus/primitivesoftware Jim Cone San Francisco, USA "Jeff" wrote in message Is there a way to have different length sheet names like Sheet1 - Sheet20 and Red1 - Red15 in the same workbook and sort them all alphabetically first then numerically? Thanks! Jeff "Chip Pearson" wrote: Change the Mid(Worksheets(N).Name, 6) to Mid(Worksheets(N).Name, 4) in all four instances. The number in the Mid statement should be the character position of the first number in the worksheet name. Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a different workbook that the sheet are not named Sheet1 - Sheet 33 but it is named Red1 - Red15. The code errors out if they are not named Sheet. Any Ideas? Thanks! Jeff "Chip Pearson" wrote: Jeff, Try 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 CInt(Mid(Worksheets(N).Name, 6)) CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If CInt(Mid(Worksheets(N).Name, 6)) < CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jeff" wrote in message ... I have a workbook that has sheets in it that are sheet1 - sheet33. I tried to use the code from http://cpearson.com/excel/sortws.htm The problem is that it does not handle the numbers in the sheet in the right order. It puts them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas? Thanks! Jeff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
| Sorting Sheets in a Workbook | Excel Programming | |||
| Sorting sheets in Workbook | Excel Programming | |||
| Sorting Sheets in a workbook | Excel Programming | |||
| Sorting Sheets in Workbook | Excel Programming | |||
Hybrid Mode
