Skip to main content

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

Author: Xiaoyang Last Modified: 2020-05-21

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


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 300 handy functions, makes your jobs more easier. 

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

1. Click Kutools Plus > 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.



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 30 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?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This doesn't appear to make a dynamic link, i.e., the value entered into the header doesn't change when the value of the cell does. So what is the purpose of the VBA code, when a copy/paste will do the same?
This comment was minimized by the moderator on the site
Hello, Neil,
If you want to link the cell value to the header or footer dynamically, please apply the following VBA code:

Note: You should insert the code into the sheet code not the normal Module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WorkRng As Range
Dim xStR As String
On Error Resume Next
xStR = "A1" '
Set WorkRng = Intersect(Application.ActiveSheet.Range("A1"), Target)
If WorkRng Is Nothing Then Exit Sub
Application.ActiveSheet.PageSetup.RightHeader = WorkRng.Range("A1").Value
End Sub


Please have a try, hope it can help you
This comment was minimized by the moderator on the site
maksudnya bagaimana?
This comment was minimized by the moderator on the site
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"
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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,
This comment was minimized by the moderator on the site
I want to insert four cells in header.This VB is only for one cell. How can I do it. Thanks
This comment was minimized by the moderator on the site
You can try Concatenating the value of rht four cells into a single cell and then use the single cell as the header.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations