Referring to a cell address in a Page Header of another tab

[DoctorV]

I have a sheet named AllData. In my Page Header on the tab AllData
want to refer to the contents of cell a9 on the worksheet tab name
MainForm How would I do that?

Also, I have a command button on Mainform to run some macros. How do
set the cell contents of a9 on my sheet Mainform to Now() in code tha
could go on this button? Thank

 

[Frank Kabel]

Hi
this can only be done with VBA. Put the following code in your workbook
module (not in an standard module)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
if LCase(wkSht.Name) = "alldata" then
With wkSht.PageSetup
.CenterHeader = workheets("MainForm").range("A9").value
End With
end with
Next wkSht
End Sub