Getting a TAB name to appear in a cell

[damian_r]

Howdo,
I have a workbook with several sheets, each sheet having a different name.

The front sheet is a summary sheet and in some of the cells I have manually typed in a header which is the name of the tab that the values below it come from.

When somebody renames the sheet (which we need to do from time to time) the Header no longer refers to the correct sheet.....

Is there a way that I can get the cell to automatically show a name of a tab so that when the tab name changes, the cell name text changes?

 

[My Aswer Is This]

Assuming you want the sheet name entered into row(1)
And sheet(1) name will be in Range("A1") and sheet(2).Name will be in Range("B1")

Put this script in your Master Sheet

Now any time that sheet is activated the correct sheet names will be entered.
There is no way this can just happen when any sheet name is changed. But this will update all the names every time you activate your Master Sheet.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Activate()
Dim i As Long
For i = 1 To Sheets.Count
Cells(1, i).Value = Sheets(i).Name
Next
End Sub

 

[S.H.A.D.O.]

Put this into a Module:

Function TabName()
  TabName = ActiveSheet.Name
End Function

Then reference it in a cell by using this formula:

=TabName()

I hope this helps!

 

[damian_r]

Thanks folks, I'll give them a go.

 

[damian_r]

...I couldn't get this one to work...I think I was doing the module thing wrong....

 

[S.H.A.D.O.]

Press Alt-F11 and copy and paste my code in there.

In a cell enter the formula: =TabName()

Let us know how you get on!

 

[FormR]

Is there a way that I can get the cell to automatically show a name of a tab so that when the tab name changes, the cell name text changes?

Hi, here is a formula you can try where Sheet1 is the current name of the worksheet.

=TRIM(RIGHT(SUBSTITUTE(CELL("filename",'Sheet1'!A1),"]",REPT(" ",50)),50))

 

[damian_r]

S.H.A.D.O

IT WORKED!

Thankyou

 

[S.H.A.D.O.]

You're welcome, I'm glad I could help!