Unhide Excel Headers and Footers

How can I unhide / display "headers and footers" on a worksheet while in normal view.
Normally headers and footers only appear in print preview.
Is this even possible?

Thankyou in advance.


Does anybody know is that possible?

To print out a document in Excel 2003, where header is only on the first
page and footer is only on the last page? The pages in the middle of document
must be printed without the header and footer?

Thanks forward for suggestions!


How do i change the font colors in MS Excel Header or Footer?

Is there a way to permanently save headers and footers in Excel so that they become a part of each spreadsheet automatically?


Hi all,

i was wondering if it was possible to customise the location of the header
and footer in excel file.

What i would like to do is to shift the left and right footer location
inwards towards the middle of the page. I have tried the manual way of
spacing the words but there is a limit of 255 chars for the footer and

Does anyone have a solution to this ?

Below is a rough illustration of what i am saying

inwards-> )footer

How can I change the predefined headers and footers in Excel?

I've changed the default font for Excel, but it didn't carry into the headers
or footers. How do I changed the header and footer default font?

Hi all,

i was wondering if it was possible to customise the location of the header
and footer in excel file.

What i would like to do is to shift the left and right footer location
inwards towards the middle of the page. I have tried the manual way of
spacing the words but there is a limit of 255 chars for the footer and

Does anyone have a solution to this ?

Below is a rough illustration of what i am saying

inwards-> )footer


I want to create a word template with dynamic header and footer from excel.
I created a word template using excel macro. The logic i used is to create a template format in excel, Copy those cells, create a word using macro and paste it using macro .The issue i faced is i couldnt copy the header and footer info from the template format in excel.Below is the code.Please help

Sub ControlWord()

    Dim appWD As Word.Application
    Dim appdoc As Word.Document
    Dim i As Integer
    'Application.ScreenUpdating = False
    'Sheets("template").Visible = True
    Set appWD = CreateObject("Word.Application")
    appWD.Visible = True
    FinalRow = Range("A9999").End(xlUp).Row
    For i = 2 To FinalRow
        ' Copy the name to cell B15
        'Range("A" & i).Copy Destination:=Sheets("template").Range("B15")
        Range("B" & i & ":C" & i).Copy
        Range("B28").PasteSpecial Transpose:=True
        Range("D" & i & ":E" & i).Copy
        Range("D28").PasteSpecial Transpose:=True
       Range("F" & i & ":J" & i).Copy
'With ActiveSheet.PageSetup

'.RightHeader = Range("B15").Value
'.CenterFooter = "Ver.Rev" & Range("D28").Value
'End With

       appWD.ActiveDocument.SaveAs Filename:="File" & i
        'Sheets("template").Visible = False
    Next i
End Sub

1. Enter an Excel Workbook complete with 2 worksheets.

2. Sheet1 has a set of custom header and footer.

3. Please show that the custom header and footer of Sheet1 could be
Copy-and-Paste'd onto Sheet2.

4. Regards.

I am trying to format headers and footers in an Excel 2003 Spreadsheet using VBA.

More specifically I would like to justify the the text in the Centre and Right hand Headers/Footers to align to the left. When I use the &L operator it moves the text into the Left hand header/footer. I've tried padding with spaces to manually align text, but this has no effect no matter how many spaces I input to the text.

I've also tried putting all the text into the Left hand header/footer in the hope that it will stretch across the page, but this only gives me an error (the total text is less than 255 characters).

One final point - I would like to put an ampersand (&) in as text but Excel always reads this as an operator prefix even when I try adding it as Chr(38) rather than the actual & sign. Can this be got around?

Thanks for any help or advice.

I have the following code to delete Headers and Footers in Excel 2010.

    Dim objHF                   As HeaderFooter 
    Dim objSection              As Section 
    For Each objSection In ActiveDocument.Sections 
        For Each objHF In objSection.Headers 
        Next objHF 
        For Each objHF In objSection.Footers 
        Next objHF 
    Next objSection 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This fails to compile - "User defined type not defined", with the "Dim objSection As Section" highlighted. I suspect I need a Reference, but which one? There's a lot to choose from!
Also ('cos I know this will happen again), how can I tell which "Dim"s each Reference supports?

This may seem like an odd request for help, and there may be other options that I am unaware of but....

I am looking to maintain a top header row and a bottom footer row. The sheet is 300 rows deep and If I put a freeze pane or split, the header or footer ends up scrolling off of the viewable screen.

The header range is A1:G6 The footer range is A301:G304. I am looking for 2 sub()'s. The first will check the DATA range A7:G300 and hide all empty rows between the header and footer ranges. The worksheet will display lets say 23 rows of data if the user needs to enter data in a new row I would like them to be able to run a macro that will hide the first data row and unhide the first empty data row below the last row that is not empty (in the data range), and a second macro that would do just the reverse. If there were any hidden rows at the top of the data range, the first hidden row above the first unhidden row would be unhidden, and the last row of the data range would then become a hidden row.

In essence it would allow the user to scroll through the data range and never loose sight of the header range & footer range.

I am creating a report document all created in Excel.

In worksheet1 are a number of parameters that the user enters such as:

Client NameSystem TypeSite AddressReport Type
On sheet2 I want to use this information in the headers and footers.

I have been able to do this with the worksheet_SelectionChange sub see below

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 
    Dim RepType As String, SysType As String, Client As String, Site As String 
     'Clear anything in the header and footer
    Sheet2.PageSetup.CenterHeader = "" 
    Sheet2.PageSetup.CenterFooter = "" 
    RepType = Sheet1.Range("c18").Text 
    SysType = Sheet1.Range("c19").Text 
    Client = Sheet1.Range("c20").Text 
    Site = Sheet1.Range("c21").Text 
     'Enter information into Header with first line being larger than the second line
    Sheet2.PageSetup.CenterHeader = "&04" & Chr(13) & "&B&14" & RepType & "&B&04" & Chr(13) & Chr(13) & "&10" & SysType 
     'Enter information into Footer with first line being larger than the second line
    Sheet2.PageSetup.CenterFooter = "&B&12" & Client & "&B&10" & Chr(13) & Site 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, this updates all the time and once run I lose the undo facility which I really want to keep.

I therefore want to only update the Headers and Footers on sheet 2 if one or more of the relevant cells on sheet 1 change.

A major thing I can't sort in my head is where code should be located and how to pass variables around, so please include which module the code needs to be placed in.

Your help would be appreciated.

Thank you


how do you put protection on headers and footers? I can protect the main
body of the wor d or excel format by sheet or workbook but can not protect
hte headers and footers.

I use Excel 2002. I recently got info from this group about printing headers
and footers on selected pages, and it worked very well. Now my question is;
How do I specify a multi line header (of footer), in a format similar to an
address, ie:

Street address
City, State
Zip Code

I have not been able to find code to force a Carriage Return.

Thanks in advance

Using Excel 2000 in a Win 200o O/S, is there any way special characters such
as the ampersand (&) can be displayed in a header and footer?


I'd like to fill in the document properties of the Excel document.
After i want to connect the properties to the headers and footers of the
excel file.
Now i have to change all the header and footers of the document myself.
Or does anyone have an other idea.

Thanx for the response.

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.


I want to hide the header and footer on page 1, but I do not want to hide the
header and footer on pages 2 thru ....

When I copy an Excel file, the headers and footers are missing in the copy.
Is this a bug? Whats the fix?

how do I have a new excel spreadsheet always open with pre-set headers and
footers? There must be a way to do this. I am tired of going into each
spreadsheet I open and re-entering my preffered set up; headers and footers,
row height, etc.

I have a macro for setting up margins on an excel sheet. However, i end up
losing my header and footer on other sheets when i run this macro. Please
help. thanks.

It would save me a fair amount of time if I could copy headers and footers
between sheets. Then I would only need to change a minimum of data in the
heading or footer

Answer: Select multiple tabs with click or click. Go to
File-->Page Set-up. Select Header/Footer tab. Make desired modifications.
This will apply your headers and footers to all selected sheets. CAUTION: be
sure to select only ONE tab again before editing data, or else you will be
editing data in all selected tabs.

One of the major limitations that I find there is with Excel is the ability
to display dynamic content within headers and footers. Specifically, without
adding VBA code, there is no way to place anything in the headers/footers
except for static text, the page number, the number of pages, date (in a
standard format), time (in a standard format), file name, sheet name, and

For starters, I would like to see the following new functionality:

- The ability to specify different ways of formatting dates and times. A
good way to do this would be similar to how different formats are used with
date/time fields in Word. For example, you would be able to specify &[Date
"d MMMM yyyy"]

- The ability to specify data from a worksheet cell/variable. For example,
you could specify &[Cell Sheet2!B12] to display the contents of cell B12 on

Ideally, I would like to see the ability to put anything that you can put
into a cell into each of the Left, Center, and Right sections of the header
and the footer.

-- Scott