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?

 

[Joe4]

The "Worksheet_Change" event procedure only runs when some cell is manually updated on your sheet.
If you want this to run when the file is first opened, use the "Workbook_Open" event (found in the "ThisWorkbook" module) instead.

See: Events in Excel VBA

 

[Tej92]

@Joe4 I've changed the code to the following and i'm getting this error when opening the file: Compile error in hidden module: ThisWorkbook.

Private Sub Workbook_Open(ByVal Target As Range)

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

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

End If

End Sub

 

[Fluff]

You could also replace both those formulae with

=TEXTBEFORE(TEXTAFTER(CELL("filename",A1),"[")," ",2)

 

[Joe4]

Workbook_Open does not have a Target parameter (you can see this if you look at the link I provided - also, if you select it from the drop-down, it will give you the exact structure you need).
And that makes sense, as you are not selecting or changing any specific range value when opening a workbook.

So it should look like:

Private Sub Workbook_Open()

If Sheet1.Range("AD1") <> "" Then
    Sheet1.Name = Sheet1.Range("AD1")
End If

End Sub

 

[Tej92]

@Joe4 Works perfectly now, thank you!! i completely missed the link you posted in the previous answer
@Fluff thank you for the formula, but i need to show the file name without the extension so I have to use those 2 formulas.
The only formula i could get rid of is if i can incorporate the following formula inside the VBA code?
=TRIM(LEFT(C1, FIND("^",SUBSTITUTE(C1, " ", "^",2)&"^")))

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[Fluff]

@Fluff thank you for the formula, but i need to show the file name without the extension so I have to use those 2 formulas.

But your two formulae do exactly the same as the one I suggested.

 

[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

 

[Fluff]

Ok, in C1 you can use

=TEXTBEFORE(TEXTAFTER(CELL("filename",A1),"["),"]")

and in AD1

=TEXTBEFORE(C1," ",2)