How to Rename Multiple Sheets from Multiple Workbook in One foldeer based on Master FIle-VBA Required

mr.vasanth18

New Member
Joined
Dec 6, 2011
Messages
19
Good Morning Everyone,

I have to rename multiple sheets from Multiple workbooks in same folder. Example, one folder contain 80 workbooks, each work book contain 14 sheets with different names. I had extracted all sheets names using by power query. Hereafter I can call as this as "Master file". Now I want to rename all sheets with Using VBA . This is sample Screen shot I had attached which I am expecting. In Master file I had renamed (5th Column) manually. So I need to VBA to Open Each workbook and rename based on this Master file and after rename file should be save and close.

1st Column is Name of Work book
2nd Column is Wokbook Folder Path
3rd Column is Sheet
4th Column is Current Sheet Name
5th Column is Expecting to Change New Sheet name

as.png
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try this (not tested).

VBA Code:
Sub Rename_Sheets()
    
    Dim ws As Worksheet, sht As Worksheet
    Dim r As Long, x As Long
    Dim strPath As String, strFile As String
    
    Set ws = ActiveSheet
    
    Application.ScreenUpdating = False
    
    For r = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
        strPath = ws.Cells(r, "B").Value
        strFile = ws.Cells(r, "A").Value
        If Dir(strPath & strFile) <> "" Then
            With Workbooks.Open(strPath & strFile)
                Do
                   DoEvents
                    Set sht = Nothing
                    On Error Resume Next
                    Set sht = .Sheets(ws.Cells(r, "D").Value)
                    On Error GoTo 0
                    If Not sht Is Nothing Then
                        sht.Name = ws.Cells(r, "E").Value
                        x = x + 1
                    Else
                        ws.Cells(r, "F").Value = "Not renamed"
                    End If
                    r = r + 1
                Loop While ws.Cells(r, "A").Value = strFile
                .Close SaveChanges:=True
                r = r - 1
            End With
        Else
            ws.Cells(r, "F").Value = "File not found"
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox x & " sheets renamed.", vbInformation, "Rename Sheets Complete"
    
End Sub
 
Upvote 0
Try this (not tested).

VBA Code:
Sub Rename_Sheets()
  
    Dim ws As Worksheet, sht As Worksheet
    Dim r As Long, x As Long
    Dim strPath As String, strFile As String
  
    Set ws = ActiveSheet
  
    Application.ScreenUpdating = False
  
    For r = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
        strPath = ws.Cells(r, "B").Value
        strFile = ws.Cells(r, "A").Value
        If Dir(strPath & strFile) <> "" Then
            With Workbooks.Open(strPath & strFile)
                Do
                   DoEvents
                    Set sht = Nothing
                    On Error Resume Next
                    Set sht = .Sheets(ws.Cells(r, "D").Value)
                    On Error GoTo 0
                    If Not sht Is Nothing Then
                        sht.Name = ws.Cells(r, "E").Value
                        x = x + 1
                    Else
                        ws.Cells(r, "F").Value = "Not renamed"
                    End If
                    r = r + 1
                Loop While ws.Cells(r, "A").Value = strFile
                .Close SaveChanges:=True
                r = r - 1
            End With
        Else
            ws.Cells(r, "F").Value = "File not found"
        End If
    Next r
  
    Application.ScreenUpdating = True
  
    MsgBox x & " sheets renamed.", vbInformation, "Rename Sheets Complete"
  
End Sub
Thanks for this . I will try and come back to you. But I have one doubt. How can I add folder link in this VBA. Because folder has 80 workbooks. Each files I have to run separate Macro ?
 
Upvote 0
Thanks for this . I will try and come back to you. But I have one doubt. How can I add folder link in this VBA. Because folder has 80 workbooks. Each files I have to run separate Macro ?

I'm not sure what you're asking.

Each file in your list has its path in column B. The macro uses that path to open each file.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top