reference a cell within the header

[Guest]

I would like to enter the value from a cell into a header. For Example. I
want the header to say "Schedule C2 Attachment A" with the value of cell C2
displayed within the header.

I found the below code but do not know if its possibe to adjust for what I
need.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("Sheet1").PageSetup.CenterHeader = Sheets("Sheet1").Range("A1").Value
End Sub

Thanks in advance for any help.

 

[Doug Glancy]

NikkiB,

Assuming your header is still in Sheet1:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("Sheet1").PageSetup.CenterHeader = "Schedule C2 Attachment A" &
Sheets("Sheet1").Range("C2").Value
End Sub

hth,

Doug

 

[Guest]

Doug,

I'm sorry I don't think I explained my properly.

I tried the code and if the value in cell C2= "1" my header reads as follows:
Schedule C2 Attachment A1

I was hoping there would be a way to insert the value from cell C2 between
the words Schedule and Attachment so that my header would read as follows:
Schedule 1 Attachment A

Is this possible?

I apologize for the confusion.

Thank you so much for your time.

Nikki

 

[Doug Glancy]

Nikki,

I think this will do what you want. Basically, you surround the unchanging
text in quotes and put the cell reference where you want it in the string
and then connect the various parts with ampersands:

Sheets("Sheet1").PageSetup.CenterHeader = "Schedule " &
Sheets("Sheet1").Range("C2").Value & " Attachment A"

hth,

Doug

 

[Guest]

Thank you so much for your pateince.

That worked perfectly!

 

[Doug Glancy]

You're very welcome!

Thank you so much for your pateince.

That worked perfectly!

 

[Guest]

Is there a way to do it without Macros? I'm going to send this sheet around
to a bunch of users that my have security set too high for VBA stuff.
I'm having the user type their name into a named cell on the first sheet and
want to use that cell ("name") in the footer on the second sheet.

 

[Ross Culver]

Even if the code works, the users will have to lower their security for it
to work on their computers.

Ross

 

[Bigfoot17]

This question has been repeatedly asked and answered (thankfully) but I would
like to take it a step further. How would you format the text in that
header. Let's say you wanted the verbage in the CenterHeader to be in Arial,
Bold and size=14. So far I can get the text to appear with the Cell
reference but am not able to format it.
Thanks