Fix sheet name variation with VBA

[tnt6869]

Hello all,
I am working with an Excel sheet that I am using to dump information into several public workbooks on a network. The information is placed into those workbooks in sheets that are by month (one sheet per month). I have code written to make sure the right sheet is selected based on the current date and that brings me to my problem. I need to dump this info into 4 different workbooks with the sheet named Dec, but each workbook may have the sheet named dec, decem, december, Decem, or December depending on who created or uses this workbook. How can I have VBA code look for the different name options and rename it to “Dec”


I am currently trying:


Sub Test2()
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name Like "apr*" Then

Sheets("apr").Name = "April"

Else

If sh.Name Like "Apr*" Then


'If flg = True Then
Sheets("Apr").Name = "April"

and so on for each possible name and month


Thanks for the help.
Tnt6869

 

[mikerickson]

Dim shortName as Variant

For Each shortName in Array("Jan", "Feb", "Mar")
    If LCase(sh.Name) Like LCase(shortName & "*") Then
        sh.Name = shortName
    End If
Next shortName

 

[tnt6869]

Sub Rename()
Dim shortName As Variant

For Each shortName In Array("Jan", "Feb", "Mar", "April", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec")

****I am getting a runtime error 404 code for this line*****
If LCase(sh.Name) Like LCase(shortName & "*") Then



sh.Name = shortName
End If
Next shortName
End Sub

Will this work if someone renames a sheet "November" as well?

 

[tnt6869]

Sorry Runtime error 424 not 404

 

[mikerickson]

1) in the array, I note "June", do you want the final sheet name to be "Jun"? If so, all the array elements should be 3 characters long. As is, the sheet will be renamed June AND a sheet named JunData will be skipped and not renamed. If you want the code to find names like AprData to be renamed April, the code will have to be altered.

2)I thought that you were going to insert my snippet into your existing code. Here's a full sub.

Sub Rename()
    Dim shortName As Variant
    Dim sh as Worksheet

    For Each sh in Thisworkbook.Worksheets

        For Each shortName In Array("Jan", "Feb", "Mar", "April", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec")

            If LCase(sh.Name) Like LCase(shortName & "*") Then
                sh.Name = shortName
            End If
        Next shortName

    Next sh
End Sub

 

[tnt6869]

Mikerickson, Thank you for your help. This sub works for one sheet in the workbook, is there a way for it to look at all of the sheets and correct them all if they do not match what is listed in the array?

1)in the array, I note "June" I am trying to cover myself with this one. I need to make sure that if someone renames a worksheet that my code will locate and dump the data into the right sheet. For example in the workbooks that I am using code to transfer data into the sheet for December may actually be named dec, decem, december, Decem, or December. So I really want the code to identify this and then rename the sheet "Dec". Then further down in the code it will make the "Dec" sheet active and transfer the data, this way when we change from Sep to Nov or so on the data will be transferred into the correct sheet. I do want to name the sheets for the months as listed in the array currently.


2)I will insert your code into an existing code, I find it much easier to build it a piece at a time, so I appreciate the full sub. I'm not that good....


Thanks again.

 

[mikerickson]

That code looks for any sheet that begins any of with the strings in the array, and changes it to the string in the array. e.g it will find a sheet named "January" and change its name to "Jan". If there are multiple sheets that meet the criteria (e.g. "January2010" and "January2011") the code will crash.

It should do this for all sheets that begin with any of the terms in the array. Any sheet that does not begin with one of those strings is left alone. e.g. "Data for Jan" is left alone.

The issue with "April" is that it will rename "AprilData" to "April" but will miss a sheet named "AprData"