Renaming sheet names depending on file name

[Tej92]

Hello everyone, I'm trying to rename the sheet1 to the first 2 words of the file name. I've managed to do it by the following formula in cell C1 to get the file name:=LEFT(MID(CELL("filename"),FIND("[",CELL("filename"))+1, FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1),FIND(".",MID(CELL("filename"),FIND("[",CELL("filename"))+1, FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1))-1)

then this formula to get the first 2 words:=TRIM(LEFT(C1, FIND("^",SUBSTITUTE(C1, " ", "^",2)&"^")))

and then the following VBA code to rename the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AD1")) Is Nothing Then

ActiveSheet.Name = ActiveSheet.Range("AD1")

End If

End Sub


But it doesn't change the sheet name unless i go to AD1 and click enter. the value in AD1 is correct as soon as i open the file but it doesn't change the sheet name unless enter is pressed inside the formula tab.
I'd like it to change the name of the cell with out that extra step. any idea?

 

[Tej92]

@Fluff I'm not good at expressing myself .
file name: This is file name.xlsm
I need file name in C1 as it will also be the title of the worksheet: This is file name
And now i need to change the sheet name to the file name but because some files will have a long name i'm only taking the first 2 words.
(this file has links to other files that have two sheets assigned to this workbook example this is_data and this is_Action, so having an automated sheet naming and vba code to pick that up i don't have to rewrite the code for each file name)
value in AD1: This is
and now i can use that reference in VBA to rename the sheet to the value in AD1.
Now the only thing that i can remove is AD1 if the VBA code can work that out in code.

Not sure if i'm explaining it right. I'm really sorry for all this confusion

 

[Tej92]

@Fluff that formula in AD1 works as it should but the formula for C1 is also giving the file extension. that's the formula i found online works fine even though it's quite long. So far the performance of the sheet is not altered.
thank you for the help!!

 

[Joe4]

@Fluff, might it have something to do if their Windows setting is toggled to show/not show file extensions?

 

[Tej92]

that is a lot shorter of a code and works perfectly!!! thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.