Take a Cell and add it to the Footer or Tab

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Costa - 26 Oct 2006 20:31 GMT

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.

Reply to this Message

Gary''s Student - 26 Oct 2006 20:49 GMT

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.

Signature

Gary''s Student

> 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
[quoted text clipped - 3 lines]
>
> Thanks.

Reply to this Message

Frank Costa - 26 Oct 2006 21:37 GMT

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

> This little macro will rename all the tabs to the value of the text in cell
> A1 of that tab:
[quoted text clipped - 15 lines]
> >
> > Thanks.

Reply to this Message

Gary''s Student - 27 Oct 2006 01:52 GMT

See:

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

Signature

Gary's Student

> Now I feel dumb. Where do I place that macro and how?
>
[quoted text clipped - 17 lines]
> > >
> > > Thanks.

Reply to this Message

Frank Costa - 27 Oct 2006 15:43 GMT

To All Who Helped Me.

Thanks! I appreciate it.

Frank

> See:
>
[quoted text clipped - 21 lines]
> > > >
> > > > Thanks.

Reply to this Message

Sune Fibaek - 26 Oct 2006 21:09 GMT

> 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

Signature

/Sune

Reply to this Message

Gord Dibben - 26 Oct 2006 22:45 GMT

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

>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
[quoted text clipped - 3 lines]
>
>Thanks.

Reply to this Message