Rename Sheet Name based on Workbook name

[airforceone]

just a quick question, is there a way to rename sheet name if workbook contains certain string?
i.e. MainOfficeReport.xls
if it contains the word "office" in the workbook name then change the sheet name to Office
BTW, default sheetname for all report have the same name thus the need to change it when opening and merging
I hope we have one....

 

[Joe4]

Assuming that you are on the sheet whose name you want to change when you run the code, try this:

Sub RenameSheet()
    If InStr(1, LCase(ActiveWorkbook.Name), "office") > 0 Then
        ActiveSheet.Name = "Office"
    End If
End Sub

 

[airforceone]

Assuming that you are on the sheet whose name you want to change when you run the code, try this:

Sub RenameSheet()
    If InStr(1, LCase(ActiveWorkbook.Name), "office") > 0 Then
        ActiveSheet.Name = "Office"
    End If
End Sub

thanks mate, that will do when the workbook is currently open. but what I would like to do is select multiple files on a directory then test/match each workbook
if one workbook contains a certain word, it will rename the workbook to said keyword
lets say we have 2 workbook on a folder and the keyword is "main" if workbook inside folder named is as follows "20230511maindata.xlsx" and 20230510extendeddata.xlsx" then workbook "20230511maindata.xlsx" will become "main.xlsx"
does not matter if its open or not as long as we can change the workbook name

 

[airforceone]

Did some searching and tweaking and finally got what I wanted!
if anyone with the same problem the following code works with "what" I needed....

Sub RenameMyFile()
'   AirForceOne :)
'   Microsoft Scripting Runtime is REQUIRED!

    Dim MyFSO As Scripting.FileSystemObject
    Dim objDir As Folder
    Dim FilePath As String
    Dim objFile As File
    
    FilePath = "C:\ReportGeneratorV.3\SOURCE\"    
    Set MyFSO = CreateObject("Scripting.FileSystemObject")
    
    If MyFSO.FolderExists(FilePath) Then    
        Set objDir = MyFSO.GetFolder(FilePath)
        
        For Each objFile In MyFSO.GetFolder(FilePath).Files        
            If UCase(objFile.Name) Like "*MAIN*" Then
                Debug.Print objFile.Name
                MyFSO.CopyFile objDir.PatH & "\" & objFile.Name, objDir.PatH & "\Exported.xlsx", True
                MyFSO.DeleteFile (objFile.PatH)
            End If            
        Next objFile        
    End If
    
End Sub

 

[Peter_SSs]

Glad you have it sorted and thanks for letting us know. However, it seems that the requirement has changed considerably or else the original request was very much mis-stated as there is a significant difference between a worksheet and a workbook.

change the sheet name

rename the workbook

 

[airforceone]

Glad you have it sorted and thanks for letting us know. However, it seems that the requirement has changed considerably or else the original request was very much mis-stated as there is a significant difference between a worksheet and a workbook.

oops sorry about that mate
in the heat of the moment I totally forgot it! anyway as I was replying to @Joe4 as I was simultaneously cleaning my working code both for renaming sheet based on workbook value, I was actually working on the module of renaming my workbook inside a folder thus the confusion! the code on post # 4 was the result of a working code after renaming sheet based on workbook value
Again apologies on my blunder...