Cutomised Headers

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

Barrie

Barrie,

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

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

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,

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

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