Changing same tab name across workbooks

[GARTHMAN]

Greetings! Here is my dilemma... I have 364 workbooks that contain data specific to each day. Each workbook has 6 identical tab names. The only differentiation is the daily date in the name of the workbooks (i.e. "06-02-2022 TVM CC JOURNAL ENTRY DETAIL"). We are changing banks and the tab labeled "WELLS FARGO COMPARED to INTERNAL" needs to be changed to "CHASE BANK COMPARED TO INTERNAL". I can find info on changing tabs within a single workbook, but not on how to change the tab name across multiple workbooks using VBA or otherwise. Does anyone have a code that will do what I am seeking to do? Thanks in advance.

 

[mumps]

Change the file path (in red) to the path of the folder which contains the workbooks. The macro assumes the 364 workbooks are the only workbooks in that folder and the extension is "xlsx".

Sub ReNameSheet()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbDest = Workbooks.Open(strPath & strExtension)
        Sheets("WELLS FARGO COMPARED to INTERNAL").Name = "CHASE BANK COMPARED TO INTERNAL"
        Application.DisplayAlerts = False
        ActiveWorkbook.Close True
        Application.DisplayAlerts = True
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[GARTHMAN]

Change the file path (in red) to the path of the folder which contains the workbooks. The macro assumes the 364 workbooks are the only workbooks in that folder and the extension is "xlsx" for all files.

Sub ReNameSheet()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Const strPath As String = "M:\DAILY REVENUE REVIEW\GARTH JOURNAL ENTRIES\TVM CC JE DETAIL FY 2022\12. JUN 2022\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbDest = Workbooks.Open(strPath & strExtension)
        Sheets("WELLS FARGO vs INTERNAL").Name = "CHASE BANK vs INTERNAL"
        Application.DisplayAlerts = False
        ActiveWorkbook.Close True
        Application.DisplayAlerts = True
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Thanks for the quick reply. I am not having any luck getting the code to run. Does it matter to what workbook the code is inserted? I have tried using the "06-01-2022 TVM..." file and creating the macro by navigating to >VIEW>MACRO>CREATE and also by inserting the MACRO in one of the tabs under the view code option. To test the code for only JUNE workbooks, I have changed the path as you indicated to "M:\DAILY REVENUE REVIEW\GARTH JOURNAL ENTRIES\TVM CC JE DETAIL FY 2022\12. JUN 2022". I have confirmed that all are "xlsx" files. Attached is a screen shot of the directory. I have also changed the code to reflect the correct sheet name and what it should be changed to. I am also getting an error message indicating that the VBA code cannot be saved to a macro free workbook. do the files need to be .xlsm rather than .xlsx? Appreciate your additional insights.

 

[mumps]

You can place the macro in a regular module in a new, blank workbook and run it from there. Don't place it in the sheet code module by using the view code option. If you save the file with the macro, it must be saved as a macro-enabled file which will give it an "xlsm" extension. Does the macro open the JUNE workbooks? Are you getting any other errors?

 

[GARTHMAN]

Thanks so much! Your original solution worked perfectly. I missed a space in the path name. You are the best!

 

[mumps]

You are very welcome.