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

"Brian" wrote:

It gives me a Compile Error:
Syntax Error

"fisch4bill" wrote:

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 InsertModule. 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


"Brian" wrote:

I wish i could just send the file to someone and have them do it.

"Brian" wrote:

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?