Using Excel macros to create a header from a specific cell value?

Question

Here's the problem; I need to create a macro that will pull a value from a specific cell and then insert that value in a header.
Any suggestions?

Answer 1

Here is an an example that takes the value from Range C3 in the worksheet named Sheet1 and puts the value in the right header of Sheet1. All you would need to do is change the sheet names out to the sheets where you want them and change the cell range to what you want it. Then move that one line to whichever header you want it under. If you aren't going to do anything with the other headers and footers they don't even have to be included in the coding.

With Worksheets("Sheet1").PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = Worksheets("Sheet1"). _
Range("C3").Value
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With

The space and underscore represent a line of code continuation in VB programming. If you copied and pasted that exact code into an Excel macro and you had a Sheet named Sheet1, the code would work exactly as is. It works the same as if I had Range("C3").Value directly after the dot.

If you don't want to have to name the worksheet but have it do it to whichever worksheet you have active you can replace the text:

Worksheets("WorksheetName")

With:

ActiveSheet

In the coding. Everything else would stay the same.

Answer 2

You could do it via VBA. I think it would be something along the lines of

Sub HeaderPath()
ActiveSheet.PageSetup.LeftHeader = ActiveWorkbook.~Cell Reference ~
End Sub