Take a Cell and add it to the Footer or Tab

[Guest]

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.

 

[Guest]

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.

 

[Sune Fibaek]

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.

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

 

[Guest]

Now I feel dumb. Where do I place that macro and how?

 

[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

 

[Guest]

See:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

To All Who Helped Me.

Thanks! I appreciate it.

Frank