Results 1 to 10 of 10
  1. #1
    gwtida
    Guest

    excel headers and footers

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel headers and footers

    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.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel headers and footers

    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.

  4. #4
    gwtida
    Guest

    Re: excel headers and footers

    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

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel headers and footers

    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.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel headers and footers

    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.

  7. #7
    gwtida
    Guest

    Re: excel headers and footers

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

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel headers and footers

    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.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: excel headers and footers

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: excel headers and footers

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •