Header & Footer Help

[danieldriftwood]

I'm a novice-intermediate level Excel user. I use Excel for scheduling employees. Each week, a new workbook is created with the weekly schedule. Each week, I change the Footer of Worksheet1 to reflect the new info, for example "Office Schedule 01/29/12 - 02/04/12." That Footer of Worksheet1 should also be the Header of Worksheet2 and Worksheet3. When I change the Footer of Worksheet1 for the next week's schedule, I want the Headers of Worksheet2 and Worksheet3 to change to match the Footer of Worksheet1 using some sort of macro.

Please let me know if this is possible and I appreciate any assistance.

Thanks,

Dan D.

[Sisyphus]

Hi,
I had the wrong idea and posted it only to find out that it didn't work. In case you saw it before I deleted, it was borrowed from MS Word but can't be realized in Excel.
Embarrassed as I was I decided to really help. So, a real solution - working and tested - is attached.
Enjoy your day!

[danieldriftwood]

Wow That's fantastic. I was able to tweak it a little to get it to match the wording I wanted, too! Thanks so much for the quick help.

Do you know how to make the date show as 02/09/12 as opposed to 02/09/2012? That would be the last thing I need to modify. Thanks again so much.

Dan D.

[Sisyphus]

Hi,
In the code the date is formatted as "Short date". I did this because I use dd.mm on my system and couldn't get Excel to show the month first. But you can try and replace the "Short date" in the Format() function with a mask like "mm/dd/yy" , like this:-

Code: Select all

Format(Mon, "mm/dd/yy")


Perhaps, on your system it will work as expected.

In Excel the date format is controlled by the Date and Time Settings in the Control Panel. That is where the "Short Date" is defined (as well as the Long Date). If you want a mm/dd/yy format for Short Date you can set it there but it will be applied globally. So, if you have a reason for having your Short Date set as mm/dd/yyyy in the Control Panel the above suggestion may enable you to eat your pudding and have it.