How to update all headers in Excel

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

We have an Excel Workbook with several different sheets (tabs). Some sheets
are Portrait and some are landscape. This workbook has a data input sheet for
alot of the data that is used on several of the sheets. We used the =('Input
Sheet'!D37)
to automate the data input, but we can't get the Header/Footnote to update.

Our problem is that the Header / Footnote Information is on the Data Input
Sheet already. How can we take the Data from Input Sheet and place it in the
Header / Footnote without changing all the page formatting?
 
Try this. This will setup the page (including header/footer information) for
any sheet in the workbook:

With Sheets("Your sheet name").PageSetup
.PrintArea = ""
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = "" '[Insert the data reference]
.CenterHeader = "" '[Insert the data reference]
.RightHeader = "" '[Insert the data reference]
.LeftFooter = "" '[Insert the data reference]
.CenterFooter = "" '[Insert the data reference]
.RightFooter = "" '[Insert the data reference]
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape 'or xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With

Customize it to fit your particulars. As you can see, virtually every
possible parameter is available for modification - any of these items that
you're not going to specify can safely be omitted. You can make this a UDF
and pass the varying information (header, footer, orientation, etc.) as
arguments to avoid having to duplicate these lines for each sheet in the
workbook.
 
Record a macro when you change the print setting for the master worksheet.

Delete the settings that you don't care about (it'll speed up the code).

Then you could use:

Option Explicit
Sub testme()

Dim MstrWks As Worksheet
Dim wks As Worksheet

Set MstrWks = Nothing
On Error Resume Next
Set MstrWks = ActiveWorkbook.Worksheets("MasterSheetNameHere")
On Error GoTo 0

If MstrWks Is Nothing Then
MsgBox "Design error!"
Exit Sub
End If

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = MstrWks.Name Then
'skip it
Else
'do all the work you want.
With wks.PageSetup
.LeftHeader = MstrWks.PageSetup.LeftHeader
.CenterHeader = MstrWks.PageSetup.CenterHeader
'and on and on...
End With
End If
Next wks

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Our company has a Spread Sheet with multiple Sheets (Tabs). Some of the
Sheets are Portrait and some are landscape. Sheet one is a data input sheet
(Tab name is = Input Sheet). Any Data that is used multiple times throughout
the Spread Sheet is Input here. That part was easy, but getting the
Header/Footnote to update on all sheets without changing the page formatting
is difficult.

We are trying to get the following for the Header:
Town: Cell #: D5 TEO No: Cell #: D34
Page 1 of ?
Office: Cell #: D38 Supplier Order No: Cell # D16 Appendix No:
Cell # D36

Footnote is the same on all sheets and never changes.

Can anyone please help us? I wish I could send the file to someone and have
them do it for us.
 
Is there any way to sent the Sheet to someone and let them do it. i am not
good at this.
 
Maybe someone will volunteer to help.

Or maybe you'll try it and see that it's not so difficult.
 
Hi Brian,
Give this code a try. You'll want to insert it into a Module in the VBE
(Visual Basic Editor). You get there by opening your file, and pressing
ALT+F11. In the VBE screen go to Insert>Module. Copy and paste this code into
the empty window that appears. Save your file. Close the VBE window. In your
Excel window, press ALT+F8 to bring up the listing of macros associated to
this workbook. Double click DynamicHeader to run the macro. This macro
assumes that you've already set each page as either Portrait or Landscape and
doesn't change that setting.

'code starts:
Sub DynamicHeader()
Dim sh As Integer

For sh = 1 To Sheets.Count
With Sheets(sh).PageSetup
.LeftHeader = "Town: " & Sheets("Input Sheet").Range("D5") _
.Value & vbNewLine & "Office: " & Sheets("Input
Sheet").Range("D38").Value
.CenterHeader = "TEO No: " & Sheets("Input Sheet").Range("D34") _
.Value & vbNewLine & "Supplier Order No: " & Sheets("Input Sheet") _
.Range("D16").Value
.RightHeader = "Page &P of &N" & vbNewLine & "Appendix No: " &
Sheets _
("Input Sheet").Range("D36").Value
.LeftFooter = "Left Footer if desired"
.CenterFooter = "Center Footer if desired"
.RightFooter = "Right Footer if desired"
.TopMargin = Application.InchesToPoints(1.25)
End With
Next sh

End Sub
'code ends

When you test this you'll notice that I put text into each of the available
footer options. Substitute your footer text for my sample text in whichever
option suits you best, and delete the other two option lines. Hope this helps!

Bill
 
It gives me a Compile Error:
Syntax Error

fisch4bill said:
Hi Brian,
Give this code a try. You'll want to insert it into a Module in the VBE
(Visual Basic Editor). You get there by opening your file, and pressing
ALT+F11. In the VBE screen go to Insert>Module. Copy and paste this code into
the empty window that appears. Save your file. Close the VBE window. In your
Excel window, press ALT+F8 to bring up the listing of macros associated to
this workbook. Double click DynamicHeader to run the macro. This macro
assumes that you've already set each page as either Portrait or Landscape and
doesn't change that setting.

'code starts:
Sub DynamicHeader()
Dim sh As Integer

For sh = 1 To Sheets.Count
With Sheets(sh).PageSetup
.LeftHeader = "Town: " & Sheets("Input Sheet").Range("D5") _
.Value & vbNewLine & "Office: " & Sheets("Input
Sheet").Range("D38").Value
.CenterHeader = "TEO No: " & Sheets("Input Sheet").Range("D34") _
.Value & vbNewLine & "Supplier Order No: " & Sheets("Input Sheet") _
.Range("D16").Value
.RightHeader = "Page &P of &N" & vbNewLine & "Appendix No: " &
Sheets _
("Input Sheet").Range("D36").Value
.LeftFooter = "Left Footer if desired"
.CenterFooter = "Center Footer if desired"
.RightFooter = "Right Footer if desired"
.TopMargin = Application.InchesToPoints(1.25)
End With
Next sh

End Sub
'code ends

When you test this you'll notice that I put text into each of the available
footer options. Substitute your footer text for my sample text in whichever
option suits you best, and delete the other two option lines. Hope this helps!

Bill
Click to expand...
 
Two questions:
1) What version of Excel are you using?
and
2) Does the error designate a particular line that has the syntax error? If
so please post it so it can be checked.

When I used this code on my machine it worked flawlessly. Check all of your
code for spacing and typing errors. Check the following code between the
quotes; they should each be in a single line (or multiple lines joined by the
line continuation characters):

".Value & vbNewLine & "Office: " & Sheets("Input Sheet").Range("D38").Value"

and

".RightHeader = "Page &P of &N" & vbNewLine & "Appendix No: " &
Sheets("Input Sheet").Range("D36").Value"

Bill
 
Back
Top