Link Cell Value to Header/Footer of Sheets using VBA Code

Header/Footer

Refers to lower or upper margin of a sheet while printing on the page which can be repeated through each sheet or respective sheet . The PageSetup object offers complete control over Header and Footer sections in a Sheet. Put following code in BeforePrint event of Workbook which will link cell value to footer left section.

Properties

  • CenterFooter
  • CenterHeader
  • LeftFooter
  • LeftHeader
  • RightFooter
  • RightHeader

Code example

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim oSheet As Worksheet
    For Each oSheet In ThisWorkbook.Worksheets
        Dim oPageSetup As PageSetup
        Set oPageSetup = oSheet.PageSetup
        
        'Set footer
        oPageSetup.LeftFooter = "Total Products Counts : " & oSheet.Range("CountProd").Value
        
        'Memory cleanup
        Set oPageSetup = Nothing
    Next oSheet
    
    'Memory cleanup
    Set oSheet = Nothing
End Sub

Note: I have created “CountProd” name range which I referenced as formula to link with Left Footer of each sheet

Output

Notice footer section where count is shown as 5 for the formula which I have placed in my source sheet as shown below by using COUNTA formula:

Leave a Reply

Your email address will not be published.