asked on

Footer font will not change when using macro code

I am able to update all the footers on the worksheets within my workbook, but I am having problems adjusting the footer font when running the code for the footers. Any clues?

Sub InsertName_Click()
Dim ws As Worksheet, i As Long
Dim DlrName As Name
Dim OR_Date As Name
 
Module4.Mod4_AI
 
    Application.ScreenUpdating = False
    i = 0
    For Each ws In ActiveWorkbook.Worksheets
        i = i + 1
        If i > 1 Then
            Application.StatusBar = "Changing header/footer in " & ws.Name
            With ws.PageSetup
                .CenterFooter = Worksheets("ReportCover").Range("DlrName")
                .RightFooter = Worksheets("ReportCover").Range("OR_Date")
            End With
        End If
    Next ws
    Set ws = Nothing
   Application.StatusBar = False
End Sub

Open in new window

patrickab

castlerj,

The sub can be as below... it's in the attached file.

Hope it helps

Patrick

Sub InsertName_Click()
Dim ws As Worksheet
 
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "ReportCover" Then
        Application.StatusBar = "Changing header/footer in " & ws.Name
        With ws.PageSetup
            .CenterFooter = [DlrName]
            .RightFooter = [OR_Date]
        End With
    End If
Next ws
 
End Sub

Open in new window

castlerj-01.xls

patrickab

If DlrName and OR_Date are named ranges then it doesn't matter where they are in the workbook, they can still be referred to as in my macro. I actually don't know exactly what you are wanting to do but I'm sure you get the idea from my code.

Patrick

SOLUTION

Ardhendu Sarangi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Casaletta

ASKER

Trying to adjust the footer font from 10 to 8 point when I run the macro.

SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

In order to change the font size, you will need to add &8 to the start of the header or footer text

SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Robert Casaletta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

patrickab

castlerj,

There is no need to ask for a question to be closed for you.

You could have been told that you can 'Accept multiple answers' and you can then apportion the points as you see fit. Perhaps you can take that route to closing a question next time - it's quicker and does not need to involve help from a Moderator.

Patrick