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.Colum ns(3))
lngMax = Application.WorksheetFunction.Max(.UsedRange.Colum ns(3))
If lngMin * lngMax Then
.Name = Split(.Name)(0) & " " & Format(lngMin, "yy") & "-" & Format(lngMax, "yy")
End If
End With

End Sub

[snb]

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

[jarhtmd]

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