How to name Excel worksheet based on min-max in column

[jarhtmd]

I have a workbook with several worksheets. Col C (in all sheets) contains various dates (mm-dd-yyyy). I would like to name the various worksheets using the last 2 digits in the year portion of the min & max dates.

sheet 01-05, sheet 02-11, sheet 17-17, etc

Can that be done?

[Admin]

Hi

Welcome to board !!!

give this a try. In a standard module

Option Explicit

Sub kTest()
    
    Dim WkSht       As Worksheet
    Dim lngMin      As Long
    Dim lngMax      As Long
    
    For Each WkSht In ThisWorkbook.Worksheets
        With WkSht
            lngMin = Application.WorksheetFunction.Min(WkSht.UsedRange.Columns(3))
            lngMax = Application.WorksheetFunction.Max(WkSht.UsedRange.Columns(3))
            If lngMin * lngMax Then
                .Name = Format(lngMin, "yy") & "-" & Format(lngMax, "yy")
            End If
        End With
    Next
    
End Sub

[jarhtmd]

Thanks. That does exactly what I ask for. But . . . I asked for the wrong thing.

I apologize for not stating the situation more clearly.

This workbook contains worksheets for various "categories" (Apples, Oranges, Bananas, etc) as well as worksheets for historical info for these categories. So I need the names to be "Apples 11-15", "Apples 16-17", "Oranges 01-05", "Bananas 09-16", etc. That is, yy-yy appended as a suffix to the existing names.

It also will be useful at times to be able to change only 1 sheet name, instead of all the names. In fact, after the initial mass renaming, this will probably be most useful.

[Admin]

try

Option Explicit

Sub kTest()
    
    Dim lngMin      As Long
    Dim lngMax      As Long
    
    
    With ActiveSheet
        lngMin = Application.WorksheetFunction.Min(.UsedRange.Columns(3))
        lngMax = Application.WorksheetFunction.Max(.UsedRange.Columns(3))
        If lngMin * lngMax Then
            .Name = Split(.Name)(0) & " " & Format(lngMin, "yy") & "-" & Format(lngMax, "yy")
        End If
    End With
    
End Sub

[jarhtmd]

Sorry, I've been away & haven't been able check & use this. Thanks a million. It will be very useful.

[snb]

Sub M_snb()
   ActiveSheet.Name = "snb " & Format(Application.Min(Columns(3)), "yy-") & Format(Application.Max(Columns(3)), "yy")
End Sub