View Full Version : TAB Name
garydp
09-30-2009, 09:02 AM
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
09-30-2009, 09:51 AM
See http://www.cpearson.com/excel/sortws.htm
mdmackillop
09-30-2009, 10:25 AM
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
09-30-2009, 11:16 AM
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
09-30-2009, 11:34 AM
Copy it into a code module and either assign it to a button, or just run it from the Tools>Macro>Macros...
garydp
09-30-2009, 11:41 AM
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
09-30-2009, 01:10 PM
There is a new sheet workbook event, call it from there.
garydp
09-30-2009, 01:17 PM
sorry being a compete novice. how do i do that?
mdmackillop
09-30-2009, 03:39 PM
Amend to this line
Dim WS As Worksheet, sh, i As Long, j As Long
garydp
09-30-2009, 06:31 PM
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
09-30-2009, 06:51 PM
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
10-01-2009, 07:28 AM
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
10-01-2009, 01:49 PM
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
10-01-2009, 01:58 PM
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
10-02-2009, 07:31 AM
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
12-04-2009, 01:21 AM
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
12-04-2009, 02:54 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.