Results 1 to 7 of 7

VBA Help to Sort Worksheets

Threaded View

  1. #1
    Registered User
    Join Date
    02-14-2013
    Location
    wales
    MS-Off Ver
    Excel 2010
    Posts
    64

    VBA Help to Sort Worksheets

    I have a workbook that I a having difficulty in sorting the worksheets. I want to sort the sheets starting after sheet 3 which is called "Master". I had it working where it sorted it first time that I ran the macro but the when a new sheet was inserted after the Master sheet then the sort all went wrong.
    Here's the current code and I have attached an example workbook. Would appreciate some help
    Sub Sort_Worksheets()
    
    Dim N As Integer
    Dim M As Integer
    Dim FirstWSToSort As Integer
    Dim LastWSToSort As Integer
    Dim SortDescending As Boolean
    
    SortDescending = True   'Lower Nearest Left
    'SortDescending = False 'Higest Nearest Left
    
    
    If ActiveWindow.SelectedSheets.Count = 1 Then
        FirstWSToSort = 4 'Start Sort at this sheet
        LastWSToSort = Worksheets.Count
    Else
        With ActiveWindow.SelectedSheets
            For N = 2 To .Count
                If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                    MsgBox "You cannot sort non-adjacent sheets"
                    Exit Sub
                End If
            Next N
            FirstWSToSort = .Item(1).Index
            LastWSToSort = .Item(.Count).Index
         End With
    End If
    
    For M = FirstWSToSort To LastWSToSort
        For N = M To LastWSToSort
            If SortDescending = True Then
                If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            Else
                If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
                   Worksheets(N).Move Before:=Worksheets(M)
                End If
            End If
         Next N
    Next M
    '
    Sheets("Register").Select
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. sort worksheets alphabetically and keep the data in the worksheets
    By Cynthia Woods in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-15-2013, 12:38 PM
  2. Sort all Worksheets
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2010, 09:41 AM
  3. How do I sort Worksheets by name?
    By M Skabialka in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-07-2005, 02:06 PM
  4. Sort Worksheets
    By mate in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2005, 07:06 AM
  5. Replies: 1
    Last Post: 03-10-2005, 05:06 PM

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