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.


"Dave Peterson" wrote:

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 he
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.)

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?

--

Dave Peterson
.