Mass change of tab names

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
617
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,790
Try this:

VBA Code:
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,790
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,790
Updated:

VBA Code:
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

Well-known Member
Joined
Feb 13, 2007
Messages
617

ADVERTISEMENT

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

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,790
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

Well-known Member
Joined
Feb 13, 2007
Messages
617

ADVERTISEMENT

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

Well-known Member
Joined
Feb 6, 2012
Messages
1,790
Haha, glad you found out about the hover, very useful.

TabName should be passed onto the second sub with the line

VBA Code:
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

Well-known Member
Joined
Feb 13, 2007
Messages
617
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,730
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top