Adding a formula to the header

M

Michelle

I need to create a spreadsheet that takes the data in cell K10 and places it
in the right section of the header.

If this is a macro solution I am not highly skilled in them so as much info
as possible is appreciated.

Thanks in advance.
Michelle
 
S

Sloth

Taking from the help - "You cannot insert graphics or cell references in a
header or footer. Use print titles if you want to repeat cell contents or a
graphic on every printed page."

that being said you can use this macro to change the header. Right click on
the sheet tab and click View Code. Right click on ThisWorkbook and click
Insert->Module. Insert this code in Module1...

Sub Macro1()
ActiveSheet.PageSetup.RightHeader = Cells(10, 11)
End Sub

you can then link this macro to a button. When you run the macro, it will
change the header for you according to cell K10. I know there is a way to
trigger a macro like this when you save, or when you print. Unfortunately,
my experience is quite limited, and I don't know how.
 
S

Sloth

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = Cells(10, 11)
End Sub

insert this code in "ThisWorkbook". It will trigger when you hit print, or
print preview.
 
B

bbarkman

This works well. I modified your suggestion to this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterFooter = Cells(62, 7)*100
End Sub

However, my value is looks like "53.486752138958". What should I add to
make the result look like "53%"?

Thanks in advance.
 
D

Dave Peterson

I'd use:

with ActiveSheet
.PageSetup.CenterFooter = format(.Cells(62, 7).value / 100, "#%")
end with
 
G

Gord Dibben

ActiveSheet.PageSetup.CenterFooter = Format((Cells(62, 7) * 100), "#,##0")


Gord Dibben MS Excel MVP
 
Top