Results 1 to 5 of 5

Sort worksheet by color and then by name

Threaded View

  1. #1
    Registered User
    Join Date
    04-07-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Sort worksheet by color and then by name

    Hi,

    I would like to sort my worksheet according to colors then by name. How can I do this? I have the VBA code to sort my worksheet by colors.

    Sub SortWorksheetsByColor()
    Dim i As Long
    Dim j As Long
    Dim ShtC() As Long
    Dim ShtN() As String
    Dim t, n As Long
    Dim lngSU As Long
    With Application
    lngSU = .ScreenUpdating
    .ScreenUpdating = False
    End With
    If Val(Application.Version) >= 10 Then
    With ThisWorkbook
    For i = 1 To .Worksheets.Count
    If .Worksheets(i).Visible = -1 Then
    n = n + 1
    ReDim Preserve ShtC(1 To n)
    ReDim Preserve ShtN(1 To n)
    ShtC(n) = .Worksheets(i).Tab.Color
    ShtN(n) = .Worksheets(i).Name
    End If
    Next
    For i = 1 To n
    For j = i To n
    If ShtC(j) < ShtC(i) Then
    t = ShtN(i)
    ShtN(i) = ShtN(j)
    ShtN(j) = t
    t = ShtC(i)
    ShtC(i) = ShtC(j)
    ShtC(j) = t
    End If
    Next
    Next
    If SortByAsc Then
    For i = n To 1 Step -1
    .Worksheets(CStr(ShtN(i))).Move before:=.Worksheets(1)
    Next
    Else
    For i = n To 1 Step -1
    .Worksheets(CStr(ShtN(i))).Move after:=.Worksheets(.Worksheets.Count)
    Next
    End If
    End With
    End If
    Application.ScreenUpdating = lngSU
    End Sub
    Thanks in advance for your help.
    Last edited by forest18; 04-07-2013 at 11:11 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1