Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
[Q] How to sort tabs in order of CodeName
Is there a way in VBA to sort the worksheets/tabs in the order of each
worksheet's CodeName? The code below sorts the sheets in alphabetical order, but how can change it to sort via CodeName? Sub SortWorksheets() ' sort worksheets in a workbook in ascending order Dim sCount As Integer, i As Integer, j As Integer Application.ScreenUpdating = False sCount = Sheets.Count If sCount = 1 Then Exit Sub For i = 1 To sCount - 1 For j = i + 1 To sCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Befo=Sheets(i) End If Next j Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to sort tabs in order of CodeName
Looks like I found some code that will do this. In case anyone is
interested.... Sub SortSheetsCodeName() Application.ScreenUpdating = False Dim iSheets%, i%, j%, ws As Worksheet iSheets = Sheets.Count For Each ws In Worksheets If Len(ws.CodeName) = 6 Then For i = 1 To iSheets - 1 For j = i + 1 To iSheets If Val(Right(Sheets(j).CodeName, 1)) < Val(Right(Sheets(i).CodeName, 1)) _ Then Sheets(j).Move befo=Sheets(i) Next j Next i End If Next ws For Each ws In Worksheets If Len(ws.CodeName) 6 Then For i = 1 To iSheets - 1 For j = i + 1 To iSheets If Val(Right(Sheets(j).CodeName, 2)) < Val(Right(Sheets(i).CodeName, 2)) _ Then Sheets(j).Move befo=Sheets(i) Next j Next i End If Next ws Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to sort tabs in order of CodeName
Why can't you simply use the "<","" operators with the codenames?
(Keep in my I haven't tested it... but I'm thinking a recursive function would simplify the code:) Option Explicit Option Compare Text Function SortByCodename(WS as Worksheets) As Worksheets Dim Done As Boolean Done = True For i = 1 To WS.Count - 1 If WS(i).Codename WS(i+1).Codename Then WS(i+1).Move Befo= WS(i) Done = False End If Next If Done Then Set SortByCodename = WS Else Set SortByCodename = SortByCodename( WS ) End If End Function Sub TestSortByCodename() With Application .ScreenUpdating = False With ThisWorkbook Set .Worksheets = SortByCodename( .Worksheets ) End With .ScreenUpdating = True End With End Sub On Jan 3, 8:56*pm, John wrote: Looks like I found some code that will do this. In case anyone is interested.... Sub SortSheetsCodeName() Application.ScreenUpdating = False Dim iSheets%, i%, j%, ws As Worksheet iSheets = Sheets.Count For Each ws In Worksheets * * If Len(ws.CodeName) = 6 Then * * For i = 1 To iSheets - 1 * * * * For j = i + 1 To iSheets * * * * * * If Val(Right(Sheets(j).CodeName, 1)) < Val(Right(Sheets(i).CodeName, 1)) _ * * * * * * Then Sheets(j).Move befo=Sheets(i) * * * * Next j * * Next i * * End If * * Next ws For Each ws In Worksheets If Len(ws.CodeName) 6 Then For i = 1 To iSheets - 1 For j = i + 1 To iSheets If Val(Right(Sheets(j).CodeName, 2)) < Val(Right(Sheets(i).CodeName, 2)) _ Then Sheets(j).Move befo=Sheets(i) Next j Next i End If Next ws Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using CodeName when Selecting Multi-Tabs | Excel Programming | |||
Retaining Sort order in the Sort Dialog box | Excel Discussion (Misc queries) | |||
Sort sheet based on particuilar sort order | Excel Worksheet Functions | |||
Sort sheet tabs in alphabetical or numerical order | Excel Discussion (Misc queries) | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming |