Skip to main content

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

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. 
Free Download
free full-featured in 30 days

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

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 Toolsets:  12 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...

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!
Comments (9)
No ratings yet. Be the first to rate!
Neil
Neil
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?
Reply
0
0
skyyang
skyyang     Neil
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
Reply
Report
0
0
DJ
DJ     skyyang
This comment was minimized by the moderator on the site
maksudnya bagaimana?
Reply
0
0
Dani
Dani
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"
Reply
0
0
Mickey
Mickey
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?
Reply
0
0
Claire
Claire
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,
Reply
0
0
saeid mohammadi
saeid mohammadi
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
Reply
0
0
Ridhwan
Ridhwan     saeid mohammadi
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.
Reply
0
0
Jeffrey Hulbert
Jeffrey Hulbert
This comment was minimized by the moderator on the site
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
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations