Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to sort Worksheets
I have a file with a potentially variable number of worksheets. However
all worksheets will be exactly the same except for the Summary sheet need to sort the sheets in an order based on the cell value in cel c105. So, I have a Summary sheet where I want the sheet names to b listed. Alongside, I then want the value recorded of cell c105. I woul then data sort the range based on the values. Then i rename the sheets Sheet2,Sheet3 etc by means of variables to sort them into the order an then reassign the original sheet names. Does anyone have any clever code to deal with this? Thank -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to sort Worksheets
You can skip all that by modify Chip Pearson's code to look at cell C105
http://www.cpearson.com/excel/sortws.htm 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 Worksheets(N).Range("C105") _ Worksheets(M).Range("C105) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If Worksheets(N).Range("C105") < _ Worksheets(M).Range("C105) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Regards, Tom Ogilvy "Stuart Farr " wrote in message ... I have a file with a potentially variable number of worksheets. However, all worksheets will be exactly the same except for the Summary sheetI need to sort the sheets in an order based on the cell value in cell c105. So, I have a Summary sheet where I want the sheet names to be listed. Alongside, I then want the value recorded of cell c105. I would then data sort the range based on the values. Then i rename the sheets, Sheet2,Sheet3 etc by means of variables to sort them into the order and then reassign the original sheet names. Does anyone have any clever code to deal with this? Thanks --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Help:macro to auto sort data to different worksheets... | Excel Discussion (Misc queries) | |||
How do I sort Worksheets by name? | New Users to Excel | |||
Sort Numerically Worksheets via VB when creating a new worksheets | Excel Programming | |||
HELP sort 2 worksheets | Excel Programming |