Dynamic Header / Footer

Question

Is there a way to reference a cell in a worksheet so it populates in the header or footer?

I have a number of worksheets for different reporting divisions and would like cell B2 to be a portion of the Header.

Sheet 1, B2 = Division of AAAAAAA

Sheet 2, B2 = Division of BBBBBBB

Sheet 3, B2 = Division of CCCCCCC

Sheet 4, B2 = Division of DDDDDDD

Answer 1

So two ways one you want the page setup to be done beforehand

Select Menu insert/Module In the new window, copy this :

Option explicit '  to avoid wrong variable names, this forces you to DECLARE all the variables.comments, behind coded line have to be precedeed with a simple quote like ' ' the name of the sub need to be given in own word

Sub addMyHeaders

Dim wk As Worksheet

    For Each wk In ThisWorkbook.Worksheets    

  wk.PageSetup.CenterHeader = "Division of " & wk.Range("B2").Value

Next wk

End sub

END OF DATA TO BE COPIED  

before executing a macro, thing that ther is no undo in XL macro

Press the F5 function key to run this or press F8 to execute line by line. two Before each print, the page set needt to be corrected

Press ALT F11 double click the " thisworkbook" object in the project explorer add this

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim wk As Worksheet

For Each wk In ThisWorkbook.Worksheets

    wk.PageSetup.CenterHeader = "Division of " & wk.Range("B2").Value

 Next wk

End Sub

You can also read the question Import csv data into Excel 2007 template   where my last post give some more info

 Regards JY

Answer 2

The sheets are named numerically for an update process that is run monthly.

Each sheet has the Division and for that matter the month for which the update took place in cells on the worksheet.

I was hoping that there could be code written in the header to capture the contents of a cell, much like the "&[Tab]" or &[Page] function that currently exists.  Something like &[Cell].[B1] ...

Any thoughts on this?

Answer 3

I would appreciate the instruction.

Thanks

Answer 4

Hi,

Yes, if indeed all sheets do need the the same page set-up,

you can can use my previous post.

Do you need instrction on how to use it ?

JY

Answer 5

Hi,

If you name the sheets with the Division Name, you can use it in the header sections..

Or by macro

Dim wk As Worksheet

For Each wk In ThisWorkbook.Worksheets

    wk.PageSetup.CenterHeader = "Division of " & wk.Range("B2").Value

Next wk

Regards

JY