SOLVED

Pageheader and footer

Copper Contributor

I am creating a multisheet workbook where I want to insert the contens of a cell into the pagefoot, is that possible and in that case how.

2 Replies
best response confirmed by johpeswed (Copper Contributor)
Solution

@johpeswed 

Hi

Although you can add different types of information to the Header or Footer (File Name, Sheet Name, Date, ...etc), however, using the Page Setup dialog box, you cannot grab a value from a cell in the worksheet by typing =cellReference

 

The Good News is, You can create a very simple code in VBA to do that for you.

The code should be attached to the Before Print Event of the Workbook:

  • Go To the Visual Basic Editor >>ALT + F11
  • To the Left side (Project Explorer) >> Right Click ThisWorkbook >> 
  • Select View Code
  • In the VBE>> click on the drop list to the left and select WorkBook
  • Then click on the drop list to the right and select Before Print
  • Assuming you want to link to the value in Cell C5 in each sheet,

Here is the code:

 

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Dim Sh As Worksheet

    For Each Sh In Me.Worksheets

    Sh.PageSetup.CenterFooter = Sh.Range("C5").Value & VBTab & Date

    Next Sh

End Sub

 

NB: To have in the Header use CenterHeader instead

VBTab will add a Space and I am joining to the Date

 

 

Hope that Helps

Nabil Mourad

Thank you Nabil
This was just what I wanted

johpeswed
1 best response

Accepted Solutions
best response confirmed by johpeswed (Copper Contributor)
Solution

@johpeswed 

Hi

Although you can add different types of information to the Header or Footer (File Name, Sheet Name, Date, ...etc), however, using the Page Setup dialog box, you cannot grab a value from a cell in the worksheet by typing =cellReference

 

The Good News is, You can create a very simple code in VBA to do that for you.

The code should be attached to the Before Print Event of the Workbook:

  • Go To the Visual Basic Editor >>ALT + F11
  • To the Left side (Project Explorer) >> Right Click ThisWorkbook >> 
  • Select View Code
  • In the VBE>> click on the drop list to the left and select WorkBook
  • Then click on the drop list to the right and select Before Print
  • Assuming you want to link to the value in Cell C5 in each sheet,

Here is the code:

 

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Dim Sh As Worksheet

    For Each Sh In Me.Worksheets

    Sh.PageSetup.CenterFooter = Sh.Range("C5").Value & VBTab & Date

    Next Sh

End Sub

 

NB: To have in the Header use CenterHeader instead

VBTab will add a Space and I am joining to the Date

 

 

Hope that Helps

Nabil Mourad

View solution in original post