VBA to rename multiple worksheets in multiple directories based on file/worksheet list?

Question

I have about 1,000 .xls/.xlsx workbooks each with varying numbers of worksheets. The files are located in various folders. I do have a list of file paths/names and associated worksheet names and I’m interested to know if there’s a VBA solution that could perform worksheet renaming for files and worksheets specified on a list that looks something like this:

File	Old Worksheet Name	New Worksheet Name
C:\Customers\ABC_Company\May2021.xls	Sales	Revenue
C:\Customers\ABC_Company\May2021.xls	Complaints	Tickets
C:\Customers\ABC_Company\May2021.xls	Opportunities	Prospects
C:\Suppliers\Northeast\XYZ Inc\Bills.xlsx	January	1
C:\Suppliers\Northeast\XYZ Inc\Bills.xlsx	February	2

I saw the following thread but it looked like it was relative to renaming sheets on an open workbook – I’d like to avoid manually opening each of the files if possible. renaming multiple worksheets from list using VBA

Many thanks in advance.

A simple loop using Workbooks.Open should get you started. — BigBen
You will not be able to get around opening the workbooks, but you can do that in the code. loop the file column and open each workbook. attach old sheet to a variable, change the name, close the workbook. Loop. — Scott Craner
@ScottCraner Hi, I wanted to ask a related question. Say why is it that one must open an excel to access its objects/methods and so on. Could you direct me to some sources that explains this. — Charlie

Tim Williams Tim Williams · Accepted Answer · 2021-06-28T15:52:51

For example:

Sub Tester()
    Dim rw As Range, wb As Workbook
    Set rw = ThisWorkbook.Sheets("Info").Range("A2:C2")              'start here
    Do While Application.CountA(rw) = 3                              'go until run out of info
        If rw.Cells(1).Value <> rw.Cells(1).Offset(-1, 0).Value Then 'different workbook from previous row ?
            If Not wb Is Nothing Then wb.Close True                  'save changes
            Set wb = Workbooks.Open(rw.Cells(1).Value)                'open this workbook
        End If
        wb.Sheets(rw.Cells(2).Value).Name = rw.Cells(3).Value        'rename sheet
        Set rw = rw.Offset(1, 0)                                     'next row
    Loop
    If Not wb Is Nothing Then wb.Close True
End Sub

VBA to rename multiple worksheets in multiple directories based on file/worksheet list?

1 Answers