Contents of cell in footer

B

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)
 
G

Gord Dibben

DH

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


Gord Dibben Excel MVP
 
D

dbhenkel

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
 
B

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)
 
D

dbhenkel

It has been test now and it does work.

Thanks.

Bob Phillips said:
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)
Click to expand...
 
Top