Excel - how do I enter a tab name?

Excel - how do I enter a tab name?

Didn't find your answer?

How do I enter the tab name into a cell?
Nicholas Robinson

Replies (14)

Please login or register to join the discussion.

avatar
By edhy
04th Jun 2005 06:08

Cell Values in Header / Footer
Following Macro will work:

Sub Macro1()
'Macro by Zubair Edhy
On Error Resume Next
For Each st In Sheets
st.PageSetup.LeftHeader = st.Cells(1, 1).Value
Next st
End Sub

Explanations:

Use CenterHeader, RightHeader LeftFooter CenterFooter or RightFooter instead of LeftHeader as you need

St.Cells(1,1).Value refers to Range A1 (row 1 , column 1), change this as needed.

Better Macro:

You will need to run the above macro manually,
Still better to run the macro automatically before printing, do following.

Select menu
Tools Macro Visual Basic Editor

On the Project Pane double click ThisWorkbook
In the first drop list on the right select Workbook
In second drop list select BeforePrint

You will get following blank macro

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

Between the Sub and End Sub type following code

'Macro by Zubair
On Error Resume Next
For Each st In Sheets
st.PageSetup.LeftHeader = st.Cells(1, 1).Value
Next st

In case of any problem post your e-mail, I will send you a prepared Excel file.

Regards.

Thanks (0)
avatar
By User deleted
06th Jun 2005 10:58

Thanks...
...email is [email protected]

Thanks (0)
avatar
By Bridget.Russell
10th Jun 2005 17:46

Reverse
What about the reverse? Is it possible to input say a date into cell a1 and get the tab of that same sheet to display the data in A1?

Thanks (0)
avatar
By Richard Willis
13th Jun 2005 09:28

Why bother?!
Nicholas

As long as you really mean cell a1, why not simply include this in the 'Rows to repeat at top' setting in 'sheet' tab in page setup? Of course this will not work if the required cell is below the column headings (which is essentially what this setting is for); however if this is the case, simply make a1 = required cell and do the same. It will not line up with the rest of the header, but what the heck?

Thanks (0)
avatar
By AnonymousUser
03rd Jun 2005 11:56

How about?
Public Sub KeepSheetName()

Dim i As Integer

For i = 1 To ActiveWorkbook.Worksheets.Count

Worksheets(i).Activate

Range("A1").Value = ActiveSheet.Name

Next i

End Sub

You'll need to run it from the VB editor, but you could easily assign it to a keyboard shortcut.

My tip would be to record a blank macro and then paste the above over the top. Click sequence would be:

Tools > Macro > Record New Macro > [Assign Name & Shortcut] > Ok > Stop > Tools > Macro > Macros > [Select Your Macro] > Edit

You'll see something like this:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 03/06/2005 by Eversheds
'

'
End Sub

Copy the KeepSheetName code above, then select the text in the macro from Sub to End Sub and paste the code in. Hit save, close the VB editor.

Now when you hit the shortcut you've assigned, the names of the sheets will be printed permanently into Cell A1.

Hope that helps, Iain

Thanks (0)
avatar
By listerramjet
03rd Jun 2005 12:36

how many different ways are there?
Sub TabName()
For Each c In ActiveWorkbook.Worksheets
With c.Range("a1")
.Value = c.Name
End With
Next
End Sub

this is similar in approach to Iain's vba, except that Iain's activates each worksheet in the workbook, whereas this one does not.

So far solutions have covered formula, user defined formula, vba, and print headers. I wonder how many other solutions there are?

Thanks (0)
avatar
By davidgough
03rd Jun 2005 12:44

A slight enhancement
=MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),(LEN(CELL("filename",A1)))-(FIND("]",CELL("filename",A1))))

Once you have extracted the tab name you can then manipulate it if you need to.

If for instance the tab name is a date you can use the datevalue function to turn the extracted text into an excel date which can be manipulated.

Regards

David

Thanks (0)
avatar
By AnonymousUser
03rd Jun 2005 16:31

David
How is that an enhancement over using the integer 255 as the third argument of the MID() function, that being as far as I can tell the only difference between our solutions?

(LEN(CELL("filename",A1)))-(FIND("]",CELL("filename",A1)))

would appear to achieve the same effect at the expense of four extra function calls. I accept that the overhead will not be noticeable to the user, but I am wondering what is the point?

Thanks (0)
avatar
By User deleted
03rd Jun 2005 20:18

All very complicated...
...Thanks for all the help. While we have the experts on board is it possible to include the contents of a cell in a header and footer?

For example: -

I would like to include the contents of sheet 1 cell a1 in the header and footer of each sheet in a 3 sheet document?

Thanks (0)
avatar
By AnonymousUser
03rd Jun 2005 11:49

Headers
Is there a particular reason why you haven't just used the header to display the worksheet title ? It would seem an infintely easier option to me - but perhaps i'm over simplifying things.

Thanks (0)
avatar
By listerramjet
02nd Jun 2005 15:44

ain't excel grand!
This applies to Excel 2000

if you want the tab name in a different cell then the following formula will assist:

=CELL("address",sheet1!A1)

this returns "[Book1]sheet1!$A$1"

change tab name to Blighty and it will substitute sheet1 with Blighty. This does not work if you want the tab name in the same tab!


To achieve this could consider a user defined function along the lines of:

Function ReturnTabName() As String
ReturnTabName = ActiveSheet.name
End Function

then in a cell in the required tab enter =ReturnTabName()

If the tab name changes then have to reenter the formula (F2 and then enter does this).

Thanks (0)
avatar
By User deleted
02nd Jun 2005 15:49

Aint Acountingweb grand??
Thank you guys!!

I would have spent half of my life trying to figure that one out!!

Slight problem with it Clint.

I have several sheets within the file. Whenever I move sheets and start keying the tab formula sets the name to that tab.

ie say I have sheet 1, sheet 2, sheet 3. I want each sheet to have the tab name in cell a1. As it stands the formual seemed to work initially. So I did have the tab names in each a1 cell but now when I move into sheet 2 and start typing all three a1 cells read "sheet 2".

Any ideas??

Thanks (0)
avatar
By AnonymousUser
02nd Jun 2005 16:15

Change formula to
=MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),255)

If there is a chance of later deleting row 1 or column A, then use INDIRECT("A1") instead of A1, or better still, use the reference of the cell that contains the formula instead of A1.

Thanks (0)
avatar
By AnonymousUser
02nd Jun 2005 15:33

As below
=MID(CELL("filename"),(FIND("]",CELL("filename"))+1),255)

Worksheet has to have been saved for the function to work, else will return #VALUE!

Thanks (0)