VBA to create custom headers for certain worksheets in a workbook

[The_Snook]

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

[xlnitwit]

Hi,

Try this routine

Please Login or Register  to view this content.

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

[The_Snook]

Cheers xlnitwit, it works a treat!

I've merged it with an event driven macro I use to time stamp the footer with the last saved detail. How can I tweak it so that the font (in the header only) is set to bold Arial size 10?

Please Login or Register  to view this content.

[xlnitwit]

Just change the first part to

Please Login or Register  to view this content.

[The_Snook]

Magical!

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

Popped back, rep given.

[xlnitwit]

Thank you!