Microsoft Office Forums - View Single Post - Batch replace Header and Footer and QuickStyle

Guessed · 01-11-2024, 05:36 PM

jchess

Please don't add posts inside threads which are unrelated to the existing topic. This is a new question that should be moved to the Excel Programming section - it doesn't belong in this particular area which is focused on Word VBA.

Your question has components which should be developed and tested in chunks.

First, work out what lines of code are needed to set the footer on one sheet. The easiest way to do this is to start the macro recorder, go into the Page Setup and set the right footer the way you want it, hit OK and stop the macro recorder. Then go and look at the code you recorded, it will look something like the following. I've bolded the lines which relate to your specific question although you may decide you want to include some of the other lines if they also need to be standardised across the workbooks:

Code:

Sub Macro1()
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "My right side text in footer"
        .LeftMargin = Application.InchesToPoints(0.708661417322835)
        .RightMargin = Application.InchesToPoints(0.708661417322835)
        .TopMargin = Application.InchesToPoints(0.748031496062992)
        .BottomMargin = Application.InchesToPoints(0.748031496062992)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 1200
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = 211
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
End Sub

Once you have your relevant lines, you can put them into a structure that updates the right footer in ALL sheets in the current workbook. This can't be recorded so you need to write it along the following lines using info harvested from your recording. If you aren't touching any other footer settings then you can simplify the With/End With parts too.

Code:

Sub MyRightFoot()
  Dim aWB As Workbook, aSht As Worksheet
  Set aWB = ActiveWorkbook
  For Each aSht In aWB.Sheets
    aSht.PageSetup.RightFooter = "My right side text in footer"
  Next aSht
End Sub

If you test that code and are happy with the results on a couple of test workbooks, you can work out how to apply that to a folder containing many workbooks. I'm sure you can find batch macros in the Excel Programming section of this site but if not, do a wider Google search.