TAB Name

[garydp]

Hi

Is there anyway of organiseing the Tab names into order?

When my worksheets are created they are named by date, at the moment it places the new tab to the left of the active sheet. if for instance i have a tab names 21-09-09 and another named 23-09-09 and wnat to create one named 22-09-09 is there away i can get it to put this between the two other tabs??

Thanks

[Bob Phillips]

See http://www.cpearson.com/excel/sortws.htm

[mdmackillop]

Sub SortSheets()
Dim WS As Worksheet, sh As Sheet, i As Long, j As Long
Set WS = Sheets.Add
For Each sh In Sheets
i = i + 1
WS.Cells(i, 1) = sh.name
Next
WS.Range(Cells(1, 1), Cells(i, 1)).Sort Cells(1, 1), xlAscending
For j = 1 To i
Sheets(WS.Cells(j, 1).Value).Move After:=Sheets(Sheets.Count)
Next
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
End Sub

[garydp]

thanks for the reply

xld how do i run the function im having a memory block

mdmackillop i get an error when i run your code. when it gets to the sh as sheet decleration i get compile error: user-defined type not defined

thanks

[Bob Phillips]

Copy it into a code module and either assign it to a button, or just run it from the Tools>Macro>Macros...

[garydp]

but i want to sort the tabs after i have created the new worksheet and given it a name. the button that is pressed to start the ball rolling creates the worksheet formats the data and then saves it. can this be added on to the end of the code?

[Bob Phillips]

There is a new sheet workbook event, call it from there.

[garydp]

sorry being a compete novice. how do i do that?

[mdmackillop]

Amend to this line
Dim WS As Worksheet, sh, i As Long, j As Long

[garydp]

Amend to this line
Dim WS As Worksheet, sh, i As Long, j As Long


ok i changed it to this but i still get an error when it gets to

WS.Range(Cells(1, 1), Cells(i, 1)).Sort Cells(1, 1), xlAscending

the error i get is run-time error '1004':

Method 'range' of object'_Worksheet' failed

also i see that the code creates a new page and puts the name of the tabs into a cell then sorts them and then will arrange the tabs in order. i have tabs that are text and with dates but i only want to arrange the tabs in date order the ones with text i dont want to move.

[garydp]

ok i got your code woking but changed the line that gave me errors to

WS.Range("A1:A2000").Sort _
Key1:=WS.Range("A1")

this does put the tabs in order but as i said before i only want the tabs that have dates to be in order.

[garydp]

ok i tried running the code again today and now i get an error

Run-Time error'9':

Subscript out of range

when it gets to the line

Sheets(WS.Cells(j, 1).Value).Move After:=Sheets(Sheets.Count)

this was ok yesterday but i am using an earlier version of excel today. 2003 today 2007 last night.

[garydp]

ignore my previous posts the code now runs without error.

i do have a problem though

at the moment i have three tabs named

13-02-2009
15-09-2009
16-02-2009

when i try and sort these using the code it puts them in the order they are now.

i tried recording a macro and when i sorted the data it was in the correct order

13-02-2009
16-02-2009
15-09-2009

the macro code is

Range("A1:A7").Select
Selection.sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

when i run this it doesnt do what i did when i recorded it and puts them in the original order. for some reason it isnt looking at them as a date and is only looking at the first set of numbers.

[mdmackillop]

You need to write the names as Dates, then turn them back to move them

Sub SortSheets()
Dim WS As Worksheet, sh, i As Long, j As Long
Dim ShNm
Set WS = Sheets.Add
For Each sh In Sheets
If IsDate(sh.Name) Then
i = i + 1
WS.Cells(i, 1) = CDate(sh.Name)
End If
Next
WS.Range(Cells(1, 1), Cells(i, 1)).Sort Cells(1, 1), xlAscending
For j = 1 To i
ShNm = Format(WS.Cells(j, 1).Value, "dd-mm-yy")
Sheets(ShNm).Move After:=Sheets(Sheets.Count)
Next
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
End Sub

[garydp]

thanks that works brilliant. one more question, when i create the sheet a combobox is created also. is it possible to assign a macro to the combobox? when i right click on it and try to assign it, it is blanked out and wont let me.

[garydp]

Back again

I have been using the code on here to sort the dates of my Tabs into order. I now have another problem were there can be two tabs or more for one date. so now the tabs can be

01/01/2001
01/01/2001 (1)
01/01/2001 (2)

and so on.

problem is when i sort the tabs the new files (1) & (2) are not put into order. can the code in post 14 be modified to sort these extra tab names??

Thanks

[Bob Phillips]

Sub SortSheets()
Dim WS As Worksheet, sh, i As Long, j As Long
Dim ShNm As String
Dim shHour As Long
Set WS = Sheets.Add
For Each sh In Sheets
ShNm = sh.Name
If InStr(sh.Name, " (") > 0 Then

ShNm = Left$(sh.Name, InStr(sh.Name, " (") - 1)
shHour = Mid$(sh.Name, InStr(sh.Name, " (") + 2, InStr(sh.Name, ")") - InStr(sh.Name, " (") - 2)
Else

shHour = 0
End If
If IsDate(ShNm) Then
i = i + 1
WS.Cells(i, 1).Value = CDate(ShNm) + shHour / 24
WS.Cells(i, 2).Value = "'" & sh.Name
End If
Next
WS.Range(Cells(1, 1), Cells(i, 2)).Sort Cells(1, 1), xlAscending
For j = 1 To i
ShNm = WS.Cells(j, 2).Value
Sheets(ShNm).Move After:=Sheets(Sheets.Count)
Next
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
End Sub