Rename worksheet in multiple Excel files within a folder

[Lcsuther]

HI,

I have a folder that contains about 30 Excel files. Each file has only one worksheet which is named the same as the file name, Which is a monthly export by month. I need the worksheet name in each file to read "Balance Sheet". How can I change all the worksheet names in 30 files at once?

Thank you, LC

 

[AC PORTA VIA]

try this

Sub OPEN_ALL_FILES_IN_FOLDER_RUN_MACRO()

Dim Path As String
'path to your folder with excel files
Path = "C:\Users\xxxxx\Desktop\folder name\"
Dim fileName As String

' ANY EXCEL FILE
fileName = Dir(Path & "*.xls*")

Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While fileName <> ""
Workbooks.Open Path & fileName

' *****************************************************add code to run on each workbook in the folder********************************************
   ActiveSheet.Name = "Balance Sheet"
' ****************************************************end code to run on each workbook in the folder*********************************************
Workbooks(fileName).Close True
fileName = Dir()
Loop
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub