Take a Cell and add it to the Footer or Tab

F

Frank Costa

I am trying to be able to take a cell that contains someone's name and either
have it automatically placed in the Footer of the document or have it be used
to name that Tab (for ease of finding which spreadsheet belongs to each
employee). Can either of these be done? I've tried to customize the footer
to include the cell ID that houses the employee's name, but it doesn't work.

Thanks.
 
G

Gary''s Student

This little macro will rename all the tabs to the value of the text in cell
A1 of that tab:

Sub Macro1()
Dim s As Worksheet
For Each s In Worksheets
s.Name = s.Range("A1").Value
Next
End Sub

just be sure that each name in A1 is unique and is a valid tab name.
 
S

Sune Fibaek

Frank said:
I am trying to be able to take a cell that contains someone's name and either
have it automatically placed in the Footer of the document or have it be used
to name that Tab (for ease of finding which spreadsheet belongs to each
employee). Can either of these be done? I've tried to customize the footer
to include the cell ID that houses the employee's name, but it doesn't work.
Click to expand...

This will customize the (center) footer:

Sub DefineFooter()
Dim sht As Worksheet
For Each sht In Worksheets
sht.PageSetup.CenterFooter = sht.Range("A1").Value
Next
End Sub
 
G

Gord Dibben

You will need a macro.

Sub CellInFooter()
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").text
End With
End Sub

OR stick it in a BeforePrint sub.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").Text
End With
End Sub


Gord Dibben MS Excel MVP

On Thu, 26 Oct 2006 12:31:02 -0700, Frank Costa <Frank
 
Top