VBA Code to Bulk Rename First Worksheet Dependent on Workbook Name

[Breader11]

I have a folder containing multiple Excel workbooks. I'd like to bulk rename the first worksheet (the typical Sheet1 position) in each workbook to match its workbook name. The worksheet names may or may not be named "Sheet1," so I don't want the code to assume anything, other than the position of the worksheet to be that first position. Any and all help would be greatly appreciated!

 

[VoG]

Try this - change the folder name to suit

Sub RenSheets()
Dim MyFolder As String
Dim MyFile As String
Dim wbname As String
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Application.ScreenUpdating = False
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
    With ActiveWorkbook
        wbname = Left(.Name, InStr(.Name, ".") - 1)
        .Sheets(1).Name = wbname
        .Close savechanges:=True
    End With
    MyFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[Breader11]

Works like magic. Thank you!

 

[Nasp]

Sorry for bumping this thread. Is it possible for the above macro to be modified to work on all subfolders of the main folder? Have been trying to do it for a while now with know success.
Thank you

 

[zamshabeer]

If I wanted the first sheet in all workbooks to be renames to "Sheet1", will replacing the following line

.Sheets(1).Name = wbname

with

.Sheets(1).Name = Sheet1

work?

 

[Lcsuther]

Try this - change the folder name to suit

Sub RenSheets()
Dim MyFolder As String
Dim MyFile As String
Dim wbname As String
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Application.ScreenUpdating = False
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
    With ActiveWorkbook
        wbname = Left(.Name, InStr(.Name, ".") - 1)
        .Sheets(1).Name = wbname
        .Close savechanges:=True
    End With
    MyFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

HI,

Can this be modified to rename several files in a folder to all have the same worksheet name "balance sheet"?