Saving as Macro Template breaks link from cell to tab name

[dianamruelas]

Hello,
I have the following formula in Cell A1 of every tab that will make the cell contain the name of the tab. There are several Macros in the workbook. When I try to save as a Macro Template, the cells no longer have the name of the tab. They are all replaced with #VALUE!, on every sheet. Please advise.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

 

[Kenneth Hobson]

'=sheetname(A1)
Function SheetName(aRange As Range) As String
  Application.Volatile (True)
  SheetName = aRange.Parent.Name
End Function

 

[dianamruelas]

Thank you so much. Could you please be specific where I put this? ...as a formula in a cell? VBA?

 

[Kenneth Hobson]

I designed as a UDF. Copy and paste the code to a Module. Use it as a UDF in any cell as commented.

 

[dianamruelas]

Any ideas how I can do this without a Macro? I liked the formula, especially since it updated automatically when the name of the tab was changed. I'm just not sure why I get the #VALUE! error once I save the document as a template.

 

[Kenneth Hobson]

Obviously, a file based on the template would not have a file path until it is saved. So, you will have to save it before a formula like that will return the result that you expect.

 

[My Aswer Is This]

If you want all your sheets to always have the sheet name in cells "A1" I would suggest this.

Install this code:

Right-click on any sheet tab
Select View Code from the pop-up context menu
Look on the top left and double click on "This Workbook"
Paste this code in the VBA edit window
Now every time you activate your sheet the sheet name will be paced in range "A1"

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Cells(1, 1).Value = ActiveSheet.Name
End Sub

This will also happen every time you create a new sheet