MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cutomised Headers


Posted by Leigh Hall on May 30, 2001 9:38 AM

Is it possible to select a cell within a worksheet and use the contents of that cell when creating a customised header?

Any help would be greatly appreciated


Posted by Barrie Davidson on May 30, 2001 9:53 AM

Hi Leigh, what do you mean by "customised header"?

Barrie

Posted by Leigh Hall on May 30, 2001 10:00 AM

Barrie,

I'm referring to the page setup header/footer options


Posted by Barrie Davidson on May 30, 2001 10:08 AM


You'll need to do this via VBA (Visual Basic for Applications), Excel's macro language. This will put the value from cell A1 to the Left Header and clear out all other headers/footers:

Sub Cust_Header()
'Written by Barrie Davidson

With ActiveSheet.PageSetup
.LeftHeader = Range("A1").Value
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With

End Sub

To change (use A1 for the right footer for example), just change where you put Range("A1").Value

Hope this is what you need. If not, let me know.

Regards,
Barrie

Posted by Leigh Hall on May 30, 2001 10:19 AM

Barrie,

Many Thanks your code worked well. Is there a way using VBA to select all the worksheets in a workbook at once and apply the custom header to them?

Regards

Leigh

Regards With ActiveSheet.PageSetup .LeftHeader = Range("A1").Value .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With End Sub To change (use A1 for the right footer for example), just change where you put Range("A1").Value Hope this is what you need. If not, let me know. Regards,

Posted by Leigh Hall on May 30, 2001 10:23 AM

Barrie,

Many thanks again.

Is it possible to put the date and time on the left of the page, the contents of cell a1 in the centre and the sheet tab name on the right? I also want to apply this globally to a number of worksheets in a workbook.

Regards

Leigh


Posted by Russell on May 30, 2001 10:32 AM


You would just add the following:

With ActiveSheet.PageSetup
.LeftHeader = "&D&T"
.CenterHeader = Range("A1").Value
.RightHeader = "&A"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With

' The &D is Date, &T is time, &A is tab

To put this on all of your worksheets, you can loop through all the sheets:

Dim xls as Worksheet

For each xls is ActiveWorkbook.Worksheets
wks.Activate
' Header code from above here
Next xls