Mahesh Kumar Yadav

Programmer

1 minute read

Let’s face it—manually updating headers and footers in every Excel report can be a repetitive and error-prone task. With a simple VBA macro, we can automate this process and make our documents more professional and consistent—without spending extra time.

VBA Code

By using just a few lines of code, we’ll be able to:

✅ Pull report details (like month and year) from worksheet cells ✅ Create stylish, easy-to-read headers
✅ Add automatic page numbering in footers

🔧 What This Macro Helps Us Do

1. Pull Values Directly from the Sheet

We can fetch key data—like the reporting month and year—using simple cell references:

month = Range("B2").Value   ' Gets the month from cell B2  
year = Range("F1").Value    ' Gets the year from cell F1

Update the cell once, and the change reflects in all printed pages.

2. Create a Clear, Bold Header

"&""Arial,Bold""&16" & month & " " & year

This sets our header in Arial Bold, size 16—clean and easy to read on any printout.

3. Add Automatic Page Numbers

"Page &P of &N"

Excel will automatically show each page number and total pages (e.g., Page 2 of 4)—great for longer reports.

🚀 Why Use This Macro?

  • Save Time: No more manual updates for every new report
  • Stay Consistent: All documents follow the same structure
  • Reduce Errors: Avoid outdated dates or incorrect page info

💡 Extra Ideas to Make It Even Better

  • Add a Company Logo Use the &G placeholder to insert a company image in the header.

  • Include File Path or Timestamp Show when and where the file was generated for better version control.

  • Use Conditional Text Adjust header content based on the report type, department, or user input.

✅ Final Thoughts

With a little automation, we can save time and improve the quality of our Excel reports. Whether we’re preparing financial summaries, team updates, or project sheets, this macro helps us present clean, well-structured documents—ready for print or PDF.

Let’s implement this macro in our next report and experience the difference.

Leave a comment

You may also enjoy