excel headers and footers

Topic

New Reply

Looking to get the same header and footer on a page by not typing it over and over again tried making a template but by doing that you can only use it on one sheet it won’t let you open the template twice and it only appers on one sheet is there a way to make a template to show up on multi sheets

Reply | Quote

Replies

Thanks for moving this question here so that others can benefit from the discussion. One additional point. If you had made this a reply to my reply to your first question, then everyone would have an easier time following the conversation.

I’m not sure what you did to get a problem with opening the template twice. With what I recommended in my previous message, you should not have to open the template at all. If the template is in the XLStart directory, it should automatically open new workbooks based on the template. You should never have to open the template. If my suggestions below do not solve your problem, could you describe exactly what you did?

In my previous reply, I may not have been completely clear. When you create the Book.xlt template file, you need to create the header and footer you want in every sheet in the template. If you want the default workbook to have three worksheets, and each one to have the default header and footer, then when you create the template, you need to have three sheeets in it and put the header and footer in all of them. Then store the workbook as a template in the XLStart directory.

Now, one other detail that I forgot. If you want new sheets that you insert into a workbook to also have this default header and footer, you need to create another template file. This file should have only one sheet in it and that sheet should have the same header and footer. Then save this file as a template named Sheet.xlt in the XLStart directory. Now, new sheets inserted in workbooks should have the default header and footer.

One other point. None of the above will affect workbooks that already exist. You will either have to manually modify those, or write a macro that will open each workbook and add the default header and footer to each sheet.

Reply | Quote

Okay I will tell you step by step what it is i’m doing
1. make the header and footer
2. save as type template (*xlt)
3. file name give it a name and .xls (example book.xlt)
4. go to file / new / and double click on the file name (example book.xlt)
5. when you go to sheet 1 it is there but when you go to sheet 2 it is not there the header and footer
6. so i went to file / new / and double click on the file name (example book.xlt) it won’t let you do it that way beacuse it is a message comes up file already open

Reply | Quote

It sounds as if you only set-up your desired header/footer on one sheet. If you want the same thing on multiple sheets, do a Group select (select left most sheet, hold down Shift key and select right most sheet) of all the sheets you want to have the same header/footer. Go to File-Page Setup-Header/Footer and enter the data you want. ALL the sheets you have selected will now have the same header/footer.

Reply | Quote

not sure what you are saying can you give me step by step directions

Reply | Quote

It looks like you are doing two things wrong.

1- You did not put the Book.xlt file into your XLStart directory. If you put it there, then it will be automatically opened when you create a new workbook. You do not need to double click on the template in the File New menu, just opening Excel should use the template, and doing a file new and double clicking the default template should also use it.

2- Headers and Footers must be set on all sheets in a workbook. When you created the template file, you needed to select Sheet1 and create the header and footer, then select Sheet2 and create the header and footer for that sheet, and do this for every sheet in the template.

Also, if you want new sheets inserted into a workbook to have the header and footer, you will need to create the Sheet.xlt file I described in another post.

Reply | Quote

Shortcut; headers and footers are broadcast to every sheet selected in group mode. So select all sheets in the workbook and then enter Print, Setup, and go for it.

Reply | Quote

If you have a sheet with a header and footer you want to copy, just go to Page Setup and click Ok. Select the other sheets and press F4. They all now have the same header and footer, (but also all the other Page Setup properties, such as print area etc).

Andrew

Reply | Quote

If you put in the following code

Private Sub Workbook_Open()
Dim Sh As Worksheet
For Each Sh In Worksheets
With Sh.PageSetup
.LeftHeader = “Hello”
.LeftFooter = “This is the end”
End With
Next
End Sub

in the ThisWorkbook module and save the workbook as a template (.xlt), then, I think, all sheets in the worksheet collection should have the header and footer as given in the macro.

Reply | Quote

This is maybe something typically for an application event. Just try this:

Open an empty workbook. This is the workbook that we will change into a template. Now put in as many worksheets as you want every new workbook to contain that is created from this template. Now you need to put in the following code:

In the ThisWorkbook module, put in

Private Sub Workbook_Open()
Call init
End Sub

Add a module and put in

Dim AppObj As New ClsApp
Sub init()
Set AppObj.AppEvent = Excel.Application
End Sub

Sub stopit()
Set AppObj = Nothing
End Sub

Add a Class module, name it ClsApp and put in

Public WithEvents AppEvent As Excel.Application

Private Sub AppEvent_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
stopit
End Sub

Private Sub AppEvent_WorkbookNewSheet(ByVal Wb As Excel.Workbook, ByVal Sh As Object)
Sh.PageSetup.CenterHeader = “Hello”
Sh.PageSetup.CenterFooter = “This is the end”
End Sub

Private Sub AppEvent_WorkbookOpen(ByVal Wb As Excel.Workbook)
Dim Sh As Worksheet
For Each Sh In Wb.Worksheets
Sh.PageSetup.CenterHeader = “Hello”
Sh.PageSetup.CenterFooter = “This is the end”
Next
End Sub

Now save your workbook as a template (e.g. HeaderFooter.xlt) and now make a new workbook using File >> New and selecting the HeaderFooter template. Every worksheet in the new workbook should contain the header “Hello” and the footer “This is the end”. Each time you insert a new worksheet in the workbook, it should automatically have the same header and footer. Alter the code as you wish.
Hope this helps.

Reply | Quote