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

[mr.vasanth18]

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

 

[AlphaFrog]

Try this (not tested).

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

 

[mr.vasanth18]

Try this (not tested).

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 ?

 

[AlphaFrog]

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.

 

[mr.vasanth18]

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.

Thanks, I understand now . Let me check and come to you if I need further help from you . Thank you so much

 

[mr.vasanth18]

Thanks, I understand now . Let me check and come to you if I need further help from you . Thank you so much

Thanks So much. I had checked and working fine.