Prev: Sheet codename not working
Next: How do I print xls file into two separate columns on the page?
From: JStiehl on 2 Jun 2010 08:22
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 before:=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
From: Don Guillett on 2 Jun 2010 09:34
Did you try the link I sent?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"JStiehl" <JStiehl(a)discussions.microsoft.com> wrote in message
news:561DDADA-4292-45A9-9F75-90767BCDC3E6(a)microsoft.com...
> 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 before:=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

From: JStiehl on 3 Jun 2010 07:26
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
> dguillett(a)gmail.com
> "JStiehl" <JStiehl(a)discussions.microsoft.com> wrote in message
> news:561DDADA-4292-45A9-9F75-90767BCDC3E6(a)microsoft.com...
> > 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 before:=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
>
> .
>
From: Don Guillett on 3 Jun 2010 08:48
Send your raw file to my email below. If desired, strip all but sheet names.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"JStiehl" <JStiehl(a)discussions.microsoft.com> wrote in message
news:E7FE6781-6332-4AFC-928F-479ED5B17262(a)microsoft.com...
> 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
>> dguillett(a)gmail.com
>> "JStiehl" <JStiehl(a)discussions.microsoft.com> wrote in message
>> news:561DDADA-4292-45A9-9F75-90767BCDC3E6(a)microsoft.com...
>> > 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 before:=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
>>
>> .
>>

From: Don Guillett on 3 Jun 2010 09:37
This could probably be refined to make an array>sort the array and move the
sheets, but it works to move the sorted numbered sheets to the front ....

Sub SAS_SortOnlyNumberedSheetsToFront()
Sheets.Add before:=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
dguillett(a)gmail.com
"Don Guillett" <dguillett1(a)gmail.com> wrote in message
news:ecUPSsxALHA.4920(a)TK2MSFTNGP04.phx.gbl...
> Send your raw file to my email below. If desired, strip all but sheet
> names.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "JStiehl" <JStiehl(a)discussions.microsoft.com> wrote in message
> news:E7FE6781-6332-4AFC-928F-479ED5B17262(a)microsoft.com...
>> 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
>>> dguillett(a)gmail.com
>>> "JStiehl" <JStiehl(a)discussions.microsoft.com> wrote in message
>>> news:561DDADA-4292-45A9-9F75-90767BCDC3E6(a)microsoft.com...
>>> > 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 before:=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
>>>
>>> .
>>>
>

First  |  Prev  | 
Pages: 1 2
Prev: Sheet codename not working
Next: How do I print xls file into two separate columns on the page?