Want Cell A1 equal to File name + Worksheet name

L

Lawrence

Hi,

i want the cell A1 equal to File name + Worksheet name.. is it possible?

Lawrence
 
J

Jacob Skaria

If you are looking for a macro try this. Launch VBE using Alt + F11. From the
left tree view double click 'This Workbook' and paste the below code. Get
back to workbook. The below code will write the bookname and sheet name to
cell A1 on Save....

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1") = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.ActiveSheet.Name
End Sub
 
J

Jacob Skaria

Using a formula, you can try this provided the workbook is already saved.

=SUBSTITUTE(CELL("filename"),INFO("directory"),)

If this post helps click Yes
 
S

Stefi

Try this formula in A1:
=CELL("filename",A1)
Regards,
Stefi

„Lawrence†ezt írta:
 
L

Lawrence

Thanks .. it works perfectly..

but just need to do some text function to make it nicer.
 
S

Stefi

You are welcome! Thanks for the feedback!
Clicking the YES button will be appreciated.
Post if you can tell us what form would be nicer for xou!
Stefi

„Lawrence†ezt írta:
 
M

mubashir aziz

This will give you File Name :

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

This will give you sheet name :

=REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")
 
Top