Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to put cell value in header/footer in Excel?

We can easily insert header or footer containing file path, file name, current date or other information with Excel’s feature, but, sometimes, we want to use a cell value as the header or footer in Excel. How can we put the contents of a cell into header or footer in the workbook?

Put cell value in header or footer of a worksheet with VBA code

Put cell value in header or footer of all worksheets with VBA code

Insert file information into header/footer with Kutools for Excel good idea3


goodInsert Workbook Information

Kutools for Excel's Insert Workbook Information utility can quickly insert sheet name, path, user name and
so on to a cell, header or footer.
doc insert workbook information
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.


If you want to place a cell content in the header or footer of the current worksheet, the following VBA code can help you. Please do as following steps:

1. Active your worksheet which you want to insert herder or footer with a cell value, then hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2.Click Insert > Module, and paste the following code in the Module Window.

VBA code: put a specified cell value in header of a worksheet

Sub HeaderFrom()
'Update 20140318
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection.Range("A1")
Set WorkRng = Application.InputBox("Range (single cell)", xTitleId, WorkRng.Address, Type:=8)
Application.ActiveSheet.PageSetup.LeftHeader = WorkRng.Range("A1").Value
End Sub

3. Then press F5 key to run this code, a dialog will pop out to remind you selecting a cell that you want to put its content into the header.doc-insert-cell-value-to-header1

4. And then click OK, this specified cell value has been inserted into the current worksheet left header. You can view the header by clicking File > Print. See screenshot:doc-insert-cell-value-to-header1

Notes:

1. If you want to use the cell content as footer of the active worksheet, you can apply this code:

VBA code: put a specified cell value in footer of a worksheet

Sub FooterFrom()
'Update 20140318
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection.Range("A1")
Set WorkRng = Application.InputBox("Range (single cell)", xTitleId, WorkRng.Address, Type:=8)
Application.ActiveSheet.PageSetup.LeftFooter = WorkRng.Range("A1").Value
End Sub

2. You can also apply the above codes for inserting cell value to right header/footer or center header/footer, just replace the LeftHeader / LeftFooter with RightHeader / RightFooter or CenterHeader / CenterFooter in the codes.


Sometimes, you want to insert header or footer with a selected cell's contents to all worksheets of your opening workbook, with the above code, you need to repeat them time and time again. In this case, the following VBA code can add the cell content to herder or footer of the whole workbook at once.

1. Active your workbook which you want to insert herder or footer with a cell value, then hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: put a specified cell value in footer of all worksheets

Sub AddFooterToAll()
'Update 20140318
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection.Range("A1")
Set WorkRng = Application.InputBox("Range (single cell)", xTitleId, WorkRng.Address, Type:=8)
For Each ws In Application.ActiveWorkbook.Worksheets
    ws.PageSetup.LeftFooter = WorkRng.Range("A1").Value
Next
End Sub

3. Then press F5 key to execute this code, a dialog will pop out to remind you selecting a cell that you want to put its content in the footer of the whole workbook.

doc-insert-cell-value-to-header1

4. And then click OK, this selected cell value has been added to all of the worksheets' left footer. You can view the footer by clicking File > Print. See screenshot:

doc-insert-cell-value-to-header1

Notes:

1. If you want to use the cell content as header of the whole workbook, you can apply this code:

VBA code: put a specified cell value in header of all worksheets

Sub AddHeaderToAll()
'Update 20140318
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection.Range("A1")
Set WorkRng = Application.InputBox("Range (single cell)", xTitleId, WorkRng.Address, Type:=8)
For Each ws In Application.ActiveWorkbook.Worksheets
    ws.PageSetup.LeftHeader = WorkRng.Range("A1").Value
Next
End Sub

2. Substitute RightHeader/Footer or CenterHeader/Footer for LeftHeader/Footer in the above codes if you want your header or footer differently positioned.


If you want to insert filer information to header or footer, such as worksheet/workbook name, workbook path and so on, you can use Kutools for Excel’s Insert Workbook Information utility if you have installed Kutools for Excel.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, please do as below:

1. Click Enterprise > Workbook > Insert Workbook Information. See screenshot:
doc kte 1

2. In the Insert Workbook Information dialog, check the information you need to insert under Information section, and check Header or Footer as you like.

Tip: you can insert the information at three location of header or footer: center, left or right.
doc insert workbook path 1

3. Click Ok. Then you can go to View > Page Layout to view the header.
doc kte 3

With Insert Workbook Information, you also can insert file information to a cell or a range of cells. Click here to know more about this utility.



Kutools for Excel: 300 + functions you must have in Excel, 60-day free trial from here

Tip.If you want to quickly split a workbook into multiple workbooks/pdf files or csv files separately, please try to use the Kutools for Excel’s Split Workbook as shown in the following screenshot. It’s full function without limitation in 60 days,please download and have a free trial now.

doc split workbook

Related articles:

How to insert file name or path into cell / header or footer in Excel?

How to insert and delete header, footer, and header picture in Excel?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Dani · 4 months ago
    Hi,

    This appears to only work once.
    What if the value of the cell changes?
    Is there a way to link the cell so the header changes when the cell value changes?

    I have a workbook where I have three sheets.

    Sheet 1 labeled "Deletion Sheet" - Sheet we send to the warehouse with info what to palatalize for the order
    Sheet 2 labeled "OA" - My order acknowledgement to the customer which pulls most of the data from the first sheet including the Order number which I need to have in my header. So I am trying to link the Header to the cell in this page with the order number (F5) which gets it's value from (C7) in the first work sheet ("Deletion Sheet")
    Sheet 3 labeled "Invoice - Invoice which also pulls most of the same information from the first sheet which would also need the header to include the value (F5) of this sheet taken from (C7) in "Deletion Sheet"
  • To post as a guest, your comment is unpublished.
    Mickey · 5 months ago
    When using your "VBA code: put a specified cell value in header of all worksheets", I would like the value placed to be formatted: Tahoma, bold, in font size 12.
    How can this be added to your code?
  • To post as a guest, your comment is unpublished.
    Claire · 4 years ago
    Hi There,

    Is there a way that you can add a cell value which takes account of applied filters?

    For example...

    A1 = Monday
    A2 = Tuesday
    A3 = Wednesday.

    Using the VBA code to display cell A1 will work initially, but once I apply a filter on days of the week, the "top" cell value is no longer A1.

    Is there a way to pick up the variable?

    Many thanks,
  • To post as a guest, your comment is unpublished.
    saeid mohammadi · 4 years ago
    I want to insert four cells in header.This VB is only for one cell.
    How can I do it.
    Thanks
    • To post as a guest, your comment is unpublished.
      Ridhwan · 1 years ago
      You can try Concatenating the value of rht four cells into a single cell and then use the single cell as the header.
  • To post as a guest, your comment is unpublished.
    Jeffrey Hulbert · 4 years ago
    I needed to insert an active payroll date range into multiple sheets. The user opens the Payroll Date sheet, enters the date range and before she prints it updates all the sheets in the workbook.

    Here is how I am able to insert multiple cells into the range with a bit less code 2010 compatible:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim WorkRng As Range
    On Error Resume Next
    For Each ws In Application.ActiveWorkbook.Worksheets
    ws.PageSetup.RightHeader = Range("'Payroll Date'!A1").Value & vbCr & Range("'Payroll Date'!A2").Value
    Next
    End Sub