Mass change of tab names

[SandsB]

I inherited 132 files all in the same folder. There are files for each month of the year and within each file there are tabs for each day of a month. Each file has the month in the filename and they're all named in this format:
XXX-# MMM YYYY.xlsx where XXX could be several different things but always 3 characters and # is either 1, 2 or 3.

Right now, all the tabs in every file are named for days in August. 08-01, 08-02, 08-03, etc. But - I need the September file's tabs named 09-01, 09-02, 09-03, etc and the November files named 11-01, 11-02,......

So in files for January in a file named PER-1 JAN 2020.xlsx there are tabs named 08-01, 08-02, 08-03, etc but I need them renamed to 01-01, 01-02, 01-03, etc.
Same for PAT-2 JAN 2020.xls and FR1-3 JAN 2020.xls. Etc, Etc, Etc,.

Can somebody show me a macro that'll rename the tabs based on the filename? Or even something that allows me to open each file individually and change the month to the correct number in the macro would be better than manually renaming thousands of tabs.

TIA

 

[mrshl9898]

Try this:

Sub renametabs()

Dim ws As Worksheet
Dim extension As String


For Each ws In Worksheets
    If Left(ws.Name, 2) = "08" Then
        extension = Mid(ws.Name, 3, 3)
        ws.Name = "01" & extension
    End If
Next ws

ActiveWorkbook.Close SaveChanges:=True

End Sub

Sub openMyfile()

    Dim Source As String
    Dim StrFile As String

    Source = "C:\Users\andrew.marshall\Desktop\Test\"
    StrFile = Dir(Source)

    Do While Len(StrFile) > 0
        Workbooks.Open Filename:=Source & StrFile
        renametabs
        StrFile = Dir()
    Loop
    
End Sub

 

[mrshl9898]

Sorry, just reread your post.

So ALL files have 08-01 08-02 etc... and you want to rename those tabs to whatever the month is in the file name?

 

[mrshl9898]

Updated:

Public TabName As String

Sub openMyfile()

    Dim Source As String
    Dim StrFile As String
    Dim MonthNm As String

    
    Source = "C:\Users\andrew.marshall\Desktop\Test\"
    StrFile = Dir(Source)
    
    MonthNm = Mid(StrFile, 7, 3)
    TabName = Format(Month(DateValue("01 " & MonthNm & " 2012")), "00")

    Do While Len(StrFile) > 0
    MonthNm = Mid(StrFile, 7, 3)
    TabName = Format(Month(DateValue("01 " & MonthNm & " 2012")), "00")
        Workbooks.Open Filename:=Source & StrFile
        renametabs
        StrFile = Dir()
    Loop
    
End Sub


Sub renametabs()

Dim ws As Worksheet
Dim extension As String


For Each ws In Worksheets
    If Left(ws.Name, 2) = "08" Then
        extension = Mid(ws.Name, 3, 3)
        ws.Name = TabName & extension
    End If
Next ws

ActiveWorkbook.Close SaveChanges:=True

End Sub

 

[SandsB]

Not sure what I'm getting wrong here. It renames the worksheets to -01, -02, -03, etc. No month.

 

[mrshl9898]

Try stepping through the code (F8), see what it says for TabName and MonthNm on the below line by hovering the mouse over them

TabName = Format(Month(DateValue("01 " & MonthNm & " 2012")), "00")

 

[SandsB]

First off, I've been doing simple macros for years and I didn't know about the hover thing. I'm sure you've saved me many hours going forward. Thanks you.

I used my new trick to confuse myself. TabName is correct in the first part but in Sub renametabs() it's "Empty"
How do I pass the value to the subroutine? Why doesn't Sub renametabs(TabName) work?

 

[mrshl9898]

Haha, glad you found out about the hover, very useful.

TabName should be passed onto the second sub with the line

Public TabName As String

Instead of Dim'ing in inside a Sub, if you Public it instead then it will apply to the Subs below it.

Did you paste it all in as displayed? Did you move it inside a routine? Are the Routines in the same module? What version of Excel are you using? (maybe it's a setting?)

 

[SandsB]

I changed where openMyfile calls renametabs to
renametabs (TabName)
and I change the subroutine to
Sub renametabs(TabName)

It works but I have some reading to do. Thanks for getting me there.