Cell Value to Equal Worksheet Name

[hshure]

I have a file where we track all of the recruiters commissions. Each worksheet is given the name of the recruiter it tracks. I would like to have the recruiter name field in the sheet read the worksheet name so that it automatically populates. Can this be done easily?

 

[Oaktree]

Welcome to the board.

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

 

[hshure]

Thank you so much. That is exactly what I needed. Just out of curiosity, is there a way to do the reverse where the tab name would read a cell value?

 

[mhenk]

Something like this:

Sub RenameWS()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ws.Name = Range("B2").Value
Next
End Sub

Where "B2" is the cell on each sheet you want to grab the tab name from.

 

[Oaktree]

Yes, but it requires VBA.

Something like:

Activesheet.name = Range("A10")

 

[daniels012]

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("D10").Value
End Sub


Michael

 

[hshure]

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("D10").Value
End Sub


Michael

So would I just paste the code above into a module? I am not very good with VBA. I know Alt+F11 to get to the VB area and then insert module.

 

[daniels012]

Right click on the tab. Select View Code.
In the little pull down you see (general) change it to worksheet. The other pull down choose Change.

Enter the code in the middle!!!

That should do it

Michael

 

[hshure]

Much thanks to everyone!!!

 

[bamaisgreat]

I have 15 Sheets and one Data Entry Sheet. Is there a formula I can put into the Data Entry sheet in different cells that would give me each sheet name ? (Example: Data Entry A1 have sheet 1 name A5 have sheet 2 name etc....
Thanks as always