Contents of cell in footer

[Guest]

I want the contents of a specific cell to be in a footer, can I do this?

 

[Bob Phillips]

This code, placed in the ThisWorkbook code module, automatically picks up
the cell value when printing.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFoot­er = Activesheet.Range("A8").Value
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Could this be modified to always pick up the value of the same cell on a
single sheet, regardless of what sheet is being printed?

I have a report that is spread out over ten worksheets. I need to have the
form number and revision date in the footer. I want this information to live
in only one place. Is there a command that would allow me to use Sheet "A",
Cell "A1" but be printing from Sheet "B"

Thanks for your help.

DH

 

[Gord Dibben]

DH

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = Sheets("A").Range("A1").Value
End Sub


Gord Dibben Excel MVP

 

[Guest]

Thanks to both responders for the help so far. Before your replies I wrote a
macro that would set the desired text for each page. I then had planned on
changing the text with a find and replace. While this works, it is
cumbersome.

I tried the coding that Gord added. And while it works the font ends up
Tahoma and 12pts. I want Tahoma and 8pts. Here is a sample of what I am
using.

Application.ScreenUpdating = False
Sheets("OBRA").Activate
With ActiveSheet.PageSetup
.LeftFooter = Sheets("PROVCERT").Range("R131").Value
.RightFooter = "&""Tahoma,Regular""&8&Z&F"
End With
Sheets("ProvCert").Activate
With ActiveSheet.PageSetup
.LeftFooter = "&""Tahoma,Regular""&8JFS 02930 (Rev. 3/2005)"
.RightFooter = ""
End With

QUESTION: Is it possible to control the font type & size using the
".LeftFooter = Sheets("PROVCERT").Range("R131").Value" coding?

Thanks again for your help.

DH

 

[Bob Phillips]

Not tested, but try this

Application.ScreenUpdating = False
Sheets("OBRA").Activate
With ActiveSheet.PageSetup
.LeftFooter = "&""Tahoma,Regular""&8" &
Sheets("PROVCERT").Range("R131").Value
.RightFooter = "&""Tahoma,Regular""&8&Z&F"
End With
Sheets("ProvCert").Activate
With ActiveSheet.PageSetup
.LeftFooter = "&""Tahoma,Regular""&8JFS 02930 (Rev. 3/2005)"
.RightFooter = ""
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

It has been test now and it does work.

Thanks.

Not tested, but try this

Application.ScreenUpdating = False
Sheets("OBRA").Activate
With ActiveSheet.PageSetup
.LeftFooter = "&""Tahoma,Regular""&8" &
Sheets("PROVCERT").Range("R131").Value
.RightFooter = "&""Tahoma,Regular""&8&Z&F"
End With
Sheets("ProvCert").Activate
With ActiveSheet.PageSetup
.LeftFooter = "&""Tahoma,Regular""&8JFS 02930 (Rev. 3/2005)"
.RightFooter = ""
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)