Results 1 to 6 of 6

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

  1. #1
    Junior Member
    Join Date
    Aug 2017
    Posts
    11
    Rep Power
    0

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

    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?

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    Welcome to board !!!

    give this a try. In a standard module

    Code:
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Aug 2017
    Posts
    11
    Rep Power
    0
    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.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    try

    Code:
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Code:
    Sub M_snb()
       ActiveSheet.Name = "snb " & Format(Application.Min(Columns(3)), "yy-") & Format(Application.Max(Columns(3)), "yy")
    End Sub

  6. #6
    Junior Member
    Join Date
    Aug 2017
    Posts
    11
    Rep Power
    0
    Sorry, I've been away & haven't been able check & use this. Thanks a million. It will be very useful.

Similar Threads

  1. Replies: 2
    Last Post: 03-08-2014, 04:22 PM
  2. Replies: 1
    Last Post: 09-18-2013, 10:17 PM
  3. Min, Max, Average Vba for Situation
    By afm1985 in forum Excel Help
    Replies: 1
    Last Post: 09-14-2013, 07:16 PM
  4. Find Value Based On MAX Value
    By Beaker Rex in forum Excel Help
    Replies: 3
    Last Post: 09-13-2011, 06:22 AM
  5. Replies: 4
    Last Post: 04-07-2011, 07:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •