How to add a footer to all sheets in a workbook using VBA?

To add a footer to all sheets in an Excel workbook using VBA, you can create a macro that loops through each worksheet and sets the footer property. Here’s a step-by-step guide on how you can accomplish this:

Open Excel and Press `ALT + F11`: This will open the VBA editor.
Insert a Module:

In the VBA editor, go to the menu and click `Insert > Module`. This will create a new module where you can write your VBA code.

Write the VBA Code:

You can use the following code that loops through all sheets and sets the footer:

   Sub AddFooterToAllSheets()
       Dim ws As Worksheet
       Dim footerText As String
       
       ' Set your desired footer text
       footerText = "Your Footer Text Here"
       
       ' Loop through each worksheet in the workbook
       For Each ws In ThisWorkbook.Worksheets
           ' Set the left, center, or right footer text
           ' Adjust as needed; here we set the center footer
           ws.PageSetup.CenterFooter = footerText
           
           ' Uncomment to use left or right footer
           ' ws.PageSetup.LeftFooter = footerText
           ' ws.PageSetup.RightFooter = footerText
       Next ws
       
       ' Notify the user that the footers have been added
       MsgBox "Footer added to all sheets successfully!"
   End Sub

In this script:

Run the Macro:

Close the VBA editor to return to Excel.
Press `ALT + F8`, select `AddFooterToAllSheets`, and click `Run`.

This macro will iterate through each worksheet in your workbook and apply the specified footer to each. You can adjust the `footerText` variable to customize your footer content.

facebooklinkedin