|
Sort tabs numerically
I need help with VBA code to sort tab names with numbers in order, but leave
the ones with words as they are. Tab names with numbers should precede the tabs with words. I have searched and can only find codes to sort alphanumerically. Thanks for your help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings |
Sort tabs numerically
See http://www.cpearson.com/excel/sortws.aspx
-- HTH Bob "JStiehl" wrote in message ... I need help with VBA code to sort tab names with numbers in order, but leave the ones with words as they are. Tab names with numbers should precede the tabs with words. I have searched and can only find codes to sort alphanumerically. Thanks for your help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings |
Sort tabs numerically
Try
Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count - 1 For lngCount2 = lngCount1 + 1 To Sheets.Count If IsNumeric(Sheets(lngCount1).Name) And _ IsNumeric(Sheets(lngCount2).Name) Then If CCur(Sheets(lngCount1).Name) CCur(Sheets(lngCount2).Name) Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For End If Next Next End Sub -- Jacob (MVP - Excel) "JStiehl" wrote: I need help with VBA code to sort tab names with numbers in order, but leave the ones with words as they are. Tab names with numbers should precede the tabs with words. I have searched and can only find codes to sort alphanumerically. Thanks for your help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings |
Sort tabs numerically
Try this instead..
Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count For lngCount2 = lngCount1 + 1 To Sheets.Count If Sheets(lngCount1).Name Sheets(lngCount2).Name Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For Next Next End Sub -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Try Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count - 1 For lngCount2 = lngCount1 + 1 To Sheets.Count If IsNumeric(Sheets(lngCount1).Name) And _ IsNumeric(Sheets(lngCount2).Name) Then If CCur(Sheets(lngCount1).Name) CCur(Sheets(lngCount2).Name) Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For End If Next Next End Sub -- Jacob (MVP - Excel) "JStiehl" wrote: I need help with VBA code to sort tab names with numbers in order, but leave the ones with words as they are. Tab names with numbers should precede the tabs with words. I have searched and can only find codes to sort alphanumerically. Thanks for your help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings |
Sort tabs numerically
Thanks Jacob. I tried both of the codes, but neither one sorted the tabs
with numbers in the proper order. I tried the following code that I found, and it will sort the tabs with numbers in the correct order, but put them at the end of all my tabs. I changed the symbol and it will put the tabs with numbers at the beginning, but will sort them in descending order. I am not very skilled at VBA--is there anything you can suggest to tweak this code to make it sort tabs with numbers in ascending order and put them at the beginning of the tabs? Thanks so much, I really appreciate your help. Sub WorksheetsSortAscending() Dim sCount As Integer, i As Integer, j As Integer sCount = Worksheets.Count If sCount = 1 Then Exit Sub For i = 1 To sCount - 1 For j = i + 1 To sCount If Val(Worksheets(j).Name) Val(Worksheets(i).Name) Then Worksheets(j).Move befo=Worksheets(i) End If Next j Next i End Sub "Jacob Skaria" wrote: Try this instead.. Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count For lngCount2 = lngCount1 + 1 To Sheets.Count If Sheets(lngCount1).Name Sheets(lngCount2).Name Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For Next Next End Sub -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Try Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count - 1 For lngCount2 = lngCount1 + 1 To Sheets.Count If IsNumeric(Sheets(lngCount1).Name) And _ IsNumeric(Sheets(lngCount2).Name) Then If CCur(Sheets(lngCount1).Name) CCur(Sheets(lngCount2).Name) Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For End If Next Next End Sub -- Jacob (MVP - Excel) "JStiehl" wrote: I need help with VBA code to sort tab names with numbers in order, but leave the ones with words as they are. Tab names with numbers should precede the tabs with words. I have searched and can only find codes to sort alphanumerically. Thanks for your help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings |
Sort tabs numerically
|
Sort tabs numerically
Hi Don,
Yes, I did try the link you sent. The problem with having it sort alphanumerically is that it sorts the tabs with words in a different order than what I need (Project1, Project2, Project3, Total, Findings). I'm looking for a way to sort just the tabs with numbers, but leave the ones with words as is. Also, it sorts the project tabs in a funky order--Project3, Project30, Project31, etc. (I didn't mention in my original post that the project tabs could go up that high). Is there anything I can do to modify that code to work as I would like it to? Thanks for your time and help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings "Don Guillett" wrote: Did you try the link I sent? -- Don Guillett Microsoft MVP Excel SalesAid Software "JStiehl" wrote in message ... Thanks Jacob. I tried both of the codes, but neither one sorted the tabs with numbers in the proper order. I tried the following code that I found, and it will sort the tabs with numbers in the correct order, but put them at the end of all my tabs. I changed the symbol and it will put the tabs with numbers at the beginning, but will sort them in descending order. I am not very skilled at VBA--is there anything you can suggest to tweak this code to make it sort tabs with numbers in ascending order and put them at the beginning of the tabs? Thanks so much, I really appreciate your help. Sub WorksheetsSortAscending() Dim sCount As Integer, i As Integer, j As Integer sCount = Worksheets.Count If sCount = 1 Then Exit Sub For i = 1 To sCount - 1 For j = i + 1 To sCount If Val(Worksheets(j).Name) Val(Worksheets(i).Name) Then Worksheets(j).Move befo=Worksheets(i) End If Next j Next i End Sub "Jacob Skaria" wrote: Try this instead.. Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count For lngCount2 = lngCount1 + 1 To Sheets.Count If Sheets(lngCount1).Name Sheets(lngCount2).Name Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For Next Next End Sub -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Try Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count - 1 For lngCount2 = lngCount1 + 1 To Sheets.Count If IsNumeric(Sheets(lngCount1).Name) And _ IsNumeric(Sheets(lngCount2).Name) Then If CCur(Sheets(lngCount1).Name) CCur(Sheets(lngCount2).Name) Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For End If Next Next End Sub -- Jacob (MVP - Excel) "JStiehl" wrote: I need help with VBA code to sort tab names with numbers in order, but leave the ones with words as they are. Tab names with numbers should precede the tabs with words. I have searched and can only find codes to sort alphanumerically. Thanks for your help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings . |
Sort tabs numerically
Send your raw file to my email below. If desired, strip all but sheet names.
-- Don Guillett Microsoft MVP Excel SalesAid Software "JStiehl" wrote in message ... Hi Don, Yes, I did try the link you sent. The problem with having it sort alphanumerically is that it sorts the tabs with words in a different order than what I need (Project1, Project2, Project3, Total, Findings). I'm looking for a way to sort just the tabs with numbers, but leave the ones with words as is. Also, it sorts the project tabs in a funky order--Project3, Project30, Project31, etc. (I didn't mention in my original post that the project tabs could go up that high). Is there anything I can do to modify that code to work as I would like it to? Thanks for your time and help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings "Don Guillett" wrote: Did you try the link I sent? -- Don Guillett Microsoft MVP Excel SalesAid Software "JStiehl" wrote in message ... Thanks Jacob. I tried both of the codes, but neither one sorted the tabs with numbers in the proper order. I tried the following code that I found, and it will sort the tabs with numbers in the correct order, but put them at the end of all my tabs. I changed the symbol and it will put the tabs with numbers at the beginning, but will sort them in descending order. I am not very skilled at VBA--is there anything you can suggest to tweak this code to make it sort tabs with numbers in ascending order and put them at the beginning of the tabs? Thanks so much, I really appreciate your help. Sub WorksheetsSortAscending() Dim sCount As Integer, i As Integer, j As Integer sCount = Worksheets.Count If sCount = 1 Then Exit Sub For i = 1 To sCount - 1 For j = i + 1 To sCount If Val(Worksheets(j).Name) Val(Worksheets(i).Name) Then Worksheets(j).Move befo=Worksheets(i) End If Next j Next i End Sub "Jacob Skaria" wrote: Try this instead.. Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count For lngCount2 = lngCount1 + 1 To Sheets.Count If Sheets(lngCount1).Name Sheets(lngCount2).Name Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For Next Next End Sub -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Try Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count - 1 For lngCount2 = lngCount1 + 1 To Sheets.Count If IsNumeric(Sheets(lngCount1).Name) And _ IsNumeric(Sheets(lngCount2).Name) Then If CCur(Sheets(lngCount1).Name) CCur(Sheets(lngCount2).Name) Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For End If Next Next End Sub -- Jacob (MVP - Excel) "JStiehl" wrote: I need help with VBA code to sort tab names with numbers in order, but leave the ones with words as they are. Tab names with numbers should precede the tabs with words. I have searched and can only find codes to sort alphanumerically. Thanks for your help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings . |
Sort tabs numerically
This could probably be refined to make an arraysort the array and move the
sheets, but it works to move the sorted numbered sheets to the front .... Sub SAS_SortOnlyNumberedSheetsToFront() Sheets.Add befo=Sheets(1) r = 1 For i = 1 To Sheets.Count If IsNumeric(Sheets(i).Name) Then Cells(r, 1) = Sheets(i).Name r = r + 1 End If Next i Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending p = 0 For Each c In ActiveSheet.UsedRange 'MsgBox CStr(c) Sheets(CStr(c)).Move After:=Sheets(p + 1) p = p + 1 Next c 'deletes dummy sheet Application.DisplayAlerts = False Sheets(1).Delete Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Send your raw file to my email below. If desired, strip all but sheet names. -- Don Guillett Microsoft MVP Excel SalesAid Software "JStiehl" wrote in message ... Hi Don, Yes, I did try the link you sent. The problem with having it sort alphanumerically is that it sorts the tabs with words in a different order than what I need (Project1, Project2, Project3, Total, Findings). I'm looking for a way to sort just the tabs with numbers, but leave the ones with words as is. Also, it sorts the project tabs in a funky order--Project3, Project30, Project31, etc. (I didn't mention in my original post that the project tabs could go up that high). Is there anything I can do to modify that code to work as I would like it to? Thanks for your time and help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings "Don Guillett" wrote: Did you try the link I sent? -- Don Guillett Microsoft MVP Excel SalesAid Software "JStiehl" wrote in message ... Thanks Jacob. I tried both of the codes, but neither one sorted the tabs with numbers in the proper order. I tried the following code that I found, and it will sort the tabs with numbers in the correct order, but put them at the end of all my tabs. I changed the symbol and it will put the tabs with numbers at the beginning, but will sort them in descending order. I am not very skilled at VBA--is there anything you can suggest to tweak this code to make it sort tabs with numbers in ascending order and put them at the beginning of the tabs? Thanks so much, I really appreciate your help. Sub WorksheetsSortAscending() Dim sCount As Integer, i As Integer, j As Integer sCount = Worksheets.Count If sCount = 1 Then Exit Sub For i = 1 To sCount - 1 For j = i + 1 To sCount If Val(Worksheets(j).Name) Val(Worksheets(i).Name) Then Worksheets(j).Move befo=Worksheets(i) End If Next j Next i End Sub "Jacob Skaria" wrote: Try this instead.. Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count For lngCount2 = lngCount1 + 1 To Sheets.Count If Sheets(lngCount1).Name Sheets(lngCount2).Name Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For Next Next End Sub -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Try Sub Macro() Dim lngCount1 As Long, lngCount2 As Long For lngCount1 = 1 To Sheets.Count - 1 For lngCount2 = lngCount1 + 1 To Sheets.Count If IsNumeric(Sheets(lngCount1).Name) And _ IsNumeric(Sheets(lngCount2).Name) Then If CCur(Sheets(lngCount1).Name) CCur(Sheets(lngCount2).Name) Then _ Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For End If Next Next End Sub -- Jacob (MVP - Excel) "JStiehl" wrote: I need help with VBA code to sort tab names with numbers in order, but leave the ones with words as they are. Tab names with numbers should precede the tabs with words. I have searched and can only find codes to sort alphanumerically. Thanks for your help. Example of what order I would like my tabs in: 123890 456678 789123 Project1 Project2 Project3 Total Findings . |
| All times are GMT +1. The time now is 10:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com