Return highest tab sheet name alpha-numerically

[renigar]

I have a workbook that has a number of worksheets named for the fiscal year in the format: 9-10, 10-11, 11-12, 12-13 (there may be more tabs than this example). I want to return the highest alphanumeric name so I can automate the creation of the next fiscal year sheet tab name and label various elements on the sheet after it's created. I'm thinking I need the max function but am drawing a blank as how to refer to the sheet names.
ex. HighName = Worksheets.Name(Max ?
Any suggestions would be appreciated.

 

[PHV]

the highest alphanumeric name
Are you aware that "9-10" is GREATER than "12-13" ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

I created some sheets:[tt]
9-10
10-11
24-25
11-12[/tt]

and use this code:

Dim i As Integer
Dim iTemp1 As Integer
Dim iTemp2 As Integer
Dim strMaxSht As String

For i = 1 To Sheets.Count
    iTemp1 = Split(Sheets(i).Name, "-")(0)
    If iTemp1 > iTemp2 Then
        iTemp2 = iTemp1
        strMaxSht = Sheets(i).Name
    End If
Next i

MsgBox "The Max Sheet name is " & strMaxSht

Some assumptions:
format of the name of the sheet is always ##-## (no spaces)
this returns the highest number of the first part of your name [blue]##[/blue]-##

Would that work for you?


Have fun.

---- Andy

 

[PHV]

Another way:

Dim i As Integer
Dim iTemp1 As Integer
Dim iTemp2 As Integer
Dim strMaxSht As String

For i = 1 To WorkSheets.Count
    iTemp1 = Val(Sheets(i).Name)
    If iTemp1 > iTemp2 Then
        iTemp2 = iTemp1
        strMaxSht = Sheets(i).Name
    End If
Next i

MsgBox "The Max Sheet name is " & strMaxSht

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

I have even a better idea.

Your question implies that all these sheets contain similar data, but are just chopped up by some criteria into different sheets.

Chopping up your data presents all sorts of problems; forseen and unforseen. It makes the analysis and use of the data much MUCH more difficult.

So why shoot yourself in the foot, tie a hand behind your back, go blindfolded & walk backwards? Get ALL your data into one sheet, from which sheet you can CONQUER THE UNIVERSE!!!

I can't help myself. It's Friday afternoon. I'm leaving!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renigar]

Thanks PHV and Andrzejek. I'll check it out Monday at work and let you know, but looks good so far. And I mistyped, it is 09-10. Skip, I always appreciate your comments and understand what your saying. The sheets are very much the same but contain fiscal year budget figures that change from year to year. There are many accounts on the sheet and my thoughts for keeping the years separate are some user is likely to look at the wrong figure and make decisions based on that wrong figure and screw the whole budget up. I realize that nothing is fool proof but I try anyway. That's my take on it.
Thanks,
renigar

 

[SkipVought]

So what happens when the head honcho asks for an analysis of trends for the last five years, like yesterday?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renigar]

The code works perfect. Thanks. Skip, That hasn't happened yet but you bring up a good point. If it were requested I would make it happen, although it would take an hour or two.

renigar