Vlookup Formula can be placed in worksheet Header or Footer

Question

I would like to know that how the Vlookup function can be placed in worksheet Header and Footer.

I did this in such a manner that placed a formula in Cell I2 or (Range("I2")) =IFERROR(VLOOKUP($G$5,Product_Details!$C$7:$L$100, 9, FALSE),"")

and used as

With ActiveSheet.PageSetup

.LeftFooter = "&""Times New Roman""&11Employee's Name & Signature: &U" & ActiveSheet.Range("I2").Value & "&U"

End With

which looks non professional. It is not automatically updated to every sheet.

Need help for solving the above problem.

Thanks & Regards

Muneeb

Answer 1

Use the Workbook_BeforePrint event to update the header before printing. Only placeholders like Dates get automatically updated in a header or footer.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  With ActiveSheet.PageSetup
      .LeftFooter = "&""Times New Roman""&11Employee's Name & Signature: &U" & ActiveSheet.Range("I2").Value & "&U"
  End With
End Sub

Put this code to the ThisWorkbookmodule.

Maybe you have to loop through all sheets - if you need the update on other worksheets as well