Forum Discussion

Keith Farmery's avatar
Keith Farmery
Copper Contributor
Jun 01, 2017

Macro to update Footers on multiple worksheets

Hi

 

Is there anyone out there who can help with this?

 

I'm creating a workbook for others to use, so I am aiming to include as much automation as possible.

 

I need to update 50 sheets, all in the same workbook so that they have a common footer based upon either a named range or a range of cells.  The named range or range of cells are located on a master sheet in the same workbook.

 

I've tried to record a macro without any success.  Using 2010, 2013 and 2016

 

Any help would be appreciated.

 

Cheers

 

 

 

 

Macros & VBA
Need Help

4 Replies

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jun 01, 2017

    Hi Keith 

     

    This is the basic code

    Sub RenameFooters()
Dim wks As Worksheet

For Each wks In Worksheets

       wks.PageSetup.LeftFooter = Range("FooterText").Value

    ' note you can use .CenterFooter or . RightFooter

    Next wks

End Sub

     

    • Keith Farmery's avatar
      Keith Farmery
      Copper Contributor
      Jun 05, 2017

      Hi Wyn

       

      That's great - thank you - your help is much appreciated.

       

      I'm going to use both methods

       

      Thanks again

       

      Keith

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Jun 01, 2017

      You could also trigger the code to run each time print is clicked

       

       

      Private Sub Workbook_BeforePrint(Cancel As Boolean)

      Dim wks As Worksheet

For Each wks In Worksheets

         wks.PageSetup.LeftFooter = Range("FooterText").Value


      Next wks

      End Sub