VBA to create custom headers for certain worksheets in a workbook

Hi,

I'd like to create custom headers for certain worksheets in my workbook. They will need to be a mix of text and a date reference from a cell in the workbook. The worksheets are called:

• Spend

• Savings

• Sector

• Customer & Supplier

• MI

The first text element will be "Performance Report".

The date (located in a tab called 'Lookup', cell C19, that has a named range of 'Lookup.MonthSelected') is input in the format xx/xx/xxxx. I'd like it to show in the header in the format mmmm yyyy.

The last text element will be as follows in relation to the list above.

• "- Spend Analysis"

• "- Savings Position"

• "- Sector Analysis"

• "- Customer & Supplier Analysis"

• "- MI Position"

For example, the header for the 'Spend' tab will be 'Performance Report November 2016 (or whatever date is selected in Lookup!C19) - Spend Analysis'

Thanks in advance,

Snook

Hi,

Try this routine

Sub setupHeaders()
Dim sheetNames
Dim headerText
Dim n As Long
sheetNames = Array("Spend", "Savings", "Sector", "Customer & Supplier", "MI")
headerText = Array("Spend Analysis", "Savings Position", "Sector Analysis", "Customer & Supplier Analysis", "MI Position")

For n = LBound(sheetNames) To UBound(sheetNames)
    Sheets(sheetNames(n)).PageSetup.CenterHeader = "Performance Report " & _
        Format$(Sheets("Lookup").Range("Lookup.MonthSelected").Value, "mmmm yyyy") & " - " & headerText(n)
Next n
End Sub

You didn't mention which part of the header to use so I put in the middle.

Just change the first part to

Sheets(sheetNames(n)).PageSetup.CenterHeader = "&""Arial,Bold""&10 Technology Pillar Performance Report " & _

Magical!

Still can't give you rep. I'll pop back at a later date.....

Popped back, rep given.

Thank you!