Organize worksheets with month names in chron order.

OaklandJim

Well-known Member
Joined
Nov 29, 2018
Messages
833
Office Version
  1. 365
Platform
  1. Windows
I have a workbook with various worksheets including month-specific ones. They are named based on the "mmm" format, so Jan, Feb, Mar, etc. I'd like to put them in chron order, so starting with Jan and ending with Dec. Dec would be the last sheet, Nov next to last, etc. There may not be a sheet for every month. Below is an array with month names in it so you don't have to type them in.

VBA Code:
Dim asMonths() As String
    
    ReDim asMonths(12)
    
    asMonths(1) = "Jan"
    asMonths(2) = "Feb"
    asMonths(3) = "Mar"
    asMonths(4) = "Apr"
    asMonths(5) = "May"
    asMonths(6) = "Jun"
    asMonths(7) = "Jul"
    asMonths(8) = "Aug"
    asMonths(9) = "Sep"
    asMonths(10) = "Oct"
    asMonths(11) = "Nov"
    asMonths(12) = "Dec"
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
...and they should be after other non-month sheets such as Sheet1 and Sheet2.
 
Upvote 0
I got it...


VBA Code:
Sub ArrangeMonthWorksheets()

    Dim asMonths() As String
    
    ReDim asMonths(12)
    
    Dim iMonth As Long
    
    Dim wsMonth As Worksheet
    
    Dim sMonthName As String
    
    asMonths(1) = "Jan"
    asMonths(2) = "Feb"
    asMonths(3) = "Mar"
    asMonths(4) = "Apr"
    asMonths(5) = "May"
    asMonths(6) = "Jun"
    asMonths(7) = "Jul"
    asMonths(8) = "Aug"
    asMonths(9) = "Sep"
    asMonths(10) = "Oct"
    asMonths(11) = "Nov"
    asMonths(12) = "Dec"
    
    For iMonth = 1 To UBound(asMonths)
    
        sMonthName = asMonths(iMonth)
        
'       Check worksheet exists.
        Set wsMonth = Nothing
        On Error Resume Next
        Set wsMonth = Worksheets(sMonthName)
        On Error GoTo 0

        If Not wsMonth Is Nothing _
         Then
            wsMonth.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        End If
    
    Next iMonth

End Sub
 
Upvote 0
What about this instead?

VBA Code:
Sub OrderSheets()
  Dim i As Long
  
  On Error Resume Next
  For i = 1 To 12
    Sheets(Left(MonthName(i), 3)).Move After:=Sheets(Sheets.Count)
  Next i
  On Error GoTo 0
End Sub
 
Upvote 0
Solution
Hey Peter. That is much better than my clunkycode. Thanks very much for the response.
 
Upvote 0
You're welcome, but it is actually simpler than that as I forgot about the optional argument in Monthname to automatically abbreviate. :oops:

VBA Code:
Sub OrderSheets()
  Dim i As Long
  
  On Error Resume Next
  For i = 1 To 12
    Sheets(MonthName(i, True)).Move After:=Sheets(Sheets.Count)
  Next i
  On Error GoTo 0
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top