Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Headers & Footers _auto update info from a cell in the worksheet
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 -- N Walton |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Headers & Footers _auto update info from a cell in the worksheet
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 --------------- Jacob Skaria "N Walton" wrote: 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 -- N Walton |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Headers & Footers _auto update info from a cell in the worksheet
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" wrote: 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 -- N Walton |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Headers & Footers _auto update info from a cell in the workshe
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 -- N Walton "Mike H" wrote: 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" wrote: 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 -- N Walton |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Headers & Footers _auto update info from a cell in the workshe
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 "N Walton" wrote: 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 -- N Walton "Mike H" wrote: 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" wrote: 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 -- N Walton |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Headers/Footers from a named range of cells in a worksheet | Excel Discussion (Misc queries) | |||
Update info from website to worksheet automatically, w/o opening s | Excel Discussion (Misc queries) | |||
Update a column data with info from new worksheet | Excel Discussion (Misc queries) | |||
Can I use cell contents to update footers in excel? | Excel Discussion (Misc queries) | |||
Can you reference cell values in Headers and Footers in Excel 200. | Excel Discussion (Misc queries) |