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

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/