Sort Sheetnames when names are Months

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 24 sheets named as Nov-2007 through to Oct-2009.

I have VBA code that names the sheet according to the content of the sheet.

The way it works, when Dec 07 comes, Nov-07 will be named "Expired" until
new data is entered in the sheet and will be named Nov-2009 but will be the
first sheet when I would like it to be the last sheet.

Is there code that will do a sort by date so Nov-2009 goes to the end,
leaving Dec-2007 as the first sheet.

Thanks
 
Hi

I have looked at the link you posted a few times but I am not excel wise
enough to get it to sort my sheets. Any assistance would be VERY much
appreciated.

Thanks
 
I forgot to add that I found a code that I can sort the sheets
alphabetically, ascending or desending, but not by date. And I have to
manually run the macro to get it to work, I would like to be triggered by the
user simply clicking in a cell in the process of entering data.

Thanks
 
Try

Sub SortWSByDate()
Dim N As Long
Dim M As Long
Dim D1 As Date
Dim D2 As Date
For N = 1 To Worksheets.Count
For M = 1 To N
D1 = DateValue(Replace(Worksheets(N).Name, "-", " 1,"))
D2 = DateValue(Replace(Worksheets(M).Name, "-", " 1,"))
If D1 < D2 Then
Worksheets(N).Move before:=Worksheets(M)
End If
Next M
Next N
End Sub

This assumes that the worksheet name can be converted to a recognizable date
by substituting " 1," for the "-" character. E.g., sheet name "Nov-2007" is
converted to "Nov 1,2007". The code doesn't change the names of the sheets,
but it must be able to convert a sheet name to a real date. The code will
fail if such a conversion cannot be made.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Chip

Thanks for chiming in.

I pasted your code into my project and when I run it I get a "Type Mismatch"
error.

The sheets get their name from a cell that is formatted as a date "mmm-yyyy"
and the code that renames the sheets also has the same date format.

This is where my lack of Excel skills comes in. Where should I be pasting
your code. I opened VBA, double clicked on a sheet name and pasted it in the
field where the code for a recorded macro would be. Incidentally, the code
that controls the sheet names is in the same spot and it works when you left
click in a cell on the page.

Thanks
 
The code works properly if the code is in the proper location and the sheet
names are valid. The code should NOT be placed in one of the Sheet modules
or in the ThisWorkbook module. Instead, go to the Insert menu in VBA and
choose Module. That will insert a code module named Module1. Paste the code
in that module. You likely got a type mismatch error because one (or more)
of the sheet names could not be converted to a real date with the DateValue
function.

Just as a test, paste the following code in the same Module1 and run it:

Sub TestNames()

Dim WS As Worksheet
Dim D As Date
Dim S As String
On Error Resume Next
For Each WS In ThisWorkbook.Worksheets
Err.Clear
D = DateValue(Replace(WS.Name, "-", " 1,"))
If Err.Number <> 0 Then
S = S & WS.Name & vbCrLf
End If
Next WS
If S = vbNullString Then
MsgBox "Sheet Names appear valid"
Else
MsgBox "The following sheet names are not valid:" & vbCrLf & S
End If

End Sub

This tests each sheet name to see if it can successfully be converted to a
date. If a sheet name cannot be converted to a date you'll have to amend the
macro to ignore that sheet, or change the name of the sheet into an
acceptable format.

Do you have other sheets in the workbook that do not have the mmm-yyyyy
format name? The code I posted sorts ALL the sheets in the workbook, while
the code on my web site at www.cpearson.com/excel/sortws.aspx allows you to
omit sheets and the beginning and/or end of the list of sheets.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Chip

You are 100% correct. I the first sheet is named "Total Hours" and obviously
won't sort.

Your error check picks it up. Without that sheet, the original code you
posted works a treat, but........

Forgive my dumbness, I cannot get it to ignore that first sheet. Your
assistance and patience is appreciated. How do I get it to ignore that first
sheet.

Thanks
 
Sometimes, you can just cheat and achieve your goals.

Instead of modifying the code to avoid the "Total Hours" worksheet, you could
rename it to a month-year that will sort where you want.

Then do the sort and then rename that worksheet to its original name:

Sub SortWSByDate()
Dim N As Long
Dim M As Long
Dim D1 As Date
Dim D2 As Date

worksheets("Total Hours").name = "Jan-1900"

For N = 1 To Worksheets.Count
For M = 1 To N
D1 = DateValue(Replace(Worksheets(N).Name, "-", " 1,"))
D2 = DateValue(Replace(Worksheets(M).Name, "-", " 1,"))
If D1 < D2 Then
Worksheets(N).Move before:=Worksheets(M)
End If
Next M
Next N

Worksheets("Jan-1900").name = "Total Hours"

End Sub

If you wanted that worksheet to the far right, you could use a date far out into
the future: Dec-3000.

(Untested and uncompiled.)
 
Thanks Chip and Dave

Changing the name of the Total Hours sheet name worked a treat to sort.

All I need to do now is have it trigger in the easiest way. At the moment I
have to run the Macro to get it to sort. What am I doing wrong or what do I
need to do.

I will have a look through some other threads, but, as always, any help is
greatly appreciated.

Thanks
 
I would put a button from the Forms toolbar onto a worksheet (the Total Hours
sheet???).

Then I could assign the macro to this button and just click when I wanted to
sort those sheets.
 
Back
Top