Entering Headers and Footers for Multiple Workbooks
You can enter the same header and footer for multiple Excel workbooks using this method.
1. Open all the workbooks you want to edit.
2. Go into Visual Basic.
3. Press CTRL + G to open the intermediate window.
4. Enter: application.Dialogs(xlDialogWorkgroup).Show
5. You will be prompted to select each worksheet of the open workbooks.
6. Select one workbook, enter the information for the header and footer and other details in Page Set-up. Click OK.
All of the workbooks will be updated.
Thanks to Danno for posting here.