Cell Reference in Header

[natetc]

Hi,

Simple question from a frustrated user.

I want to reference the text from cell B4 & B5 in the header of my sheet.

Do I have to put this in the MS VB of the sheet? or do I formulate this and drop it in the header?

thanks!

 

[Slape]

What are you wanting to reference?

There are ways to include your reference in text, such as "Today is [DATE]" and have the date pulling from another cell or there is a simple reference where you just want the exact thing from another cell to apprear in a different cell.

 

[natetc]

What are you wanting to reference?

There are ways to include your reference in text, such as "Today is [DATE]" and have the date pulling from another cell or there is a simple reference where you just want the exact thing from another cell to apprear in a different cell.

I literally want whatever is in cell B4 and B5 placed in the header as text.

So if B4 = Dog and B5 = Cat, I want the header to state Dog Cat

 

[Smitty]

You can use this in the BeforePrint event:

ActiveSheet.PageSetup.CenterHeader = ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1")

HTH,

 

[Taul]

error - missread the question

 

[natetc]

You can use this in the BeforePrint event:

ActiveSheet.PageSetup.CenterHeader = ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1")

HTH,

Sorry to do this, but where is the BeforePrint event? I went to Print Preview, Page Setup, and Header/Footer. Inputted the formula but it only shows as the text in the header.

 

[Smitty]

That's VBA code. To use it right-click ALT+F11 to open the VB Editor. Once open look in the Project Explorer on the left side of the window (if you don't see it hit CTRL+R). You'll see your workbook listed as VBAProject(WorkbookName). Expand the folder and then open the ThisWorkbook module and paste this in:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.Name = "Sheet1" Then ActiveSheet.PageSetup.CenterHeader = ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1")
End Sub

You don't need to define the sheet if you want the header on every sheet, just if you want to restrict it to a certain sheet(s).

ALT+Q will take you back to Excel.

 

[JarrodK]

Thanks for a good direction. That's a step in the right direction for me but, to get it to work, I had to create "sheet1" and put something in cells "A1" and "B1"(to match your string of code) - (couldn't get it to work last night when I replaced the sheet name with "PSA-Fee", adjusted to "LeftHeader" and my cells where "H1" and "H2"). When I enacted your string of code below it did capture the contents of cells A1 and A2. However, when I adjusted the contents of cells A1 and A2 it did not update the header. It still kept the original contents of those cells. I had to go into the header and delete the original contents for it to show the new contents on the next print. Ultimately, for my spreadsheet tab to be successful, I need to reference 5 cells in the header and throw in a page count at the bottom. That is to say I need a stack of 4 lines. The top line will be one cell reference. The second line down will be 3 cell references, the third line down will be one cell reference, and the bottom line will be a page count (Page # of #) that starts with page 2. And I need the header to update when I change the contents of those cells. I'm using 2010. Do I even stand a chance of making this work? Thank you for pointing me in the right direction and for whatever you might be able to help me with. Jarrod

 

[fvisions]

Used the following code to add a cell reference in my header
Sub headerofactivesheet()
ActiveSheet.PageSetup.CenterHeader = ActiveSheet.Range("c2") & " " & ActiveSheet.Range("c3") & " " & Format(Worksheets("Sheet1").Range("C4").Value)
End Sub

but it appears in the header as one line - Supplier Loads MWh 1/4/2017
what I want it to look like in the header is

Supplier Loads
MWh
Wednesday, January 4, 2017

Can you fix this?