Headers & Footers _auto update info from a cell in the worksheet

[N Walton]

Hi,

Is there a way I can get the "headers & footers" for all the sheets in an
excel work book to automatically update with info from a cell in the 1st
worksheet?

E.g my 1st worksheet has "Issue 3" in a cell which I would like to be
displayed in the "headers & Footers" of each sheet & automatically update the
headers & footers of each sheet when the Issue number in a cell on the 1st
sheet changes to say Issue 4.

Thanks

 

[Jacob Skaria]

You can do with using the below code..Set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From the left treeview double click 'This Workbook' and copy the below code
to the code panel..Press Alt+F11 to return to workbook. Check out print
preview. The header and footer will be set to the content in cell A1 of the
1st sheet. Try and feedback.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each ws In Me.Worksheets
ws.PageSetup.CenterFooter = Sheets(1).Range("A1")
ws.PageSetup.CenterHeader = Sheets(1).Range("A1")
Next
End Sub

If this post helps click Yes

 

[Mike H]

Hi,

Lets assume you are using A1 of Sheet 1 to hold your footer information.
Right click the sheet tab, view code and paste this in. When you change A1
all the footers will change

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
For x = 1 To Worksheets.Count
Sheets(x).PageSetup.LeftFooter = Sheets("Sheet1").Range("A1").Value
Next
End Sub


Mike

 

[N Walton]

Hi Mike,

I tried it & it didnt seem to work I kept getting debug. so I changed it
slightly (see below) as I only want the right header to auto update form cell
a1. However I have not really used this vbu code stuff before so I am a
beginner im afraid but am trying to widen my knowledge in excel. Have you any
suggestions where I am going wrong? (The debugger point at the line stating
with sheets (x).pageSetup)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
For x = 1 To Worksheets.Count
Sheets(x).PageSetup.RightHeader = Sheets("Sheet1").Range("A1").Value
Next
End Sub

 

[Mike H]

Hi,

Your code works fine for me

Are you sure your putting it in the correct place? Right click the sheet tab
of the A1 cell your using, view code and paste it in on the right and it
should work

Mike