Header Footer Editor VBA

smit3446

New Member
Joined
Nov 16, 2015
Messages
43
I'm having issue with a macro that I never used to have issues with. The code goes through the Header/Footer of each worksheet within the workbook and updates it with three lines in the Left Header:

Company Name
Program
Effective Date

It also adds in a logo that we use in the right footer and sheet name in the left footer. Here is the code:

Code:
Sub header_footer_unscale_new()Dim ws As Worksheet




For Each ws In ActiveWorkbook.Worksheets
   
    With ws.PageSetup
        .ScaleWithDocHeaderFooter = False
        .TopMargin = Application.InchesToPoints(0.9)
        .LeftHeader = "&""Arial,Bold""&8Company Name" & vbCr & "Program" & vbCr & "Effective July 1, 2018"
        .RightHeader = ""
        .LeftFooter = "&""Arial Narrow,Regular""&A"
        .RightFooter = "&G" 'specifies that you want an image in your footer
        .RightFooterPicture.Filename = "C:\Users\User1\Desktop\Excel Logo.jpg" 'specifies the image file
        
    End With
    ActiveWindow.View = xlNormalView
    Range("A1").Activate
    
Next
            
End Sub

I'm getting a Run-time error 1004 with the following line:

Code:
        .LeftHeader = "&""Arial,Bold""&8Company Name" & vbCr & "Program" & vbCr & "Effective July 1, 2018"
Is the syntax off? What am I doing wrong? Thanks in advance.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,070
.
Ran your macro just as posted and it works fine here for all sheets.

?????
 

smit3446

New Member
Joined
Nov 16, 2015
Messages
43
Well at least I know I'm not going crazy, I couldn't figure it out (still can't). I've never ran into this problem before.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,070
Sometimes my computer will act up when using Excel. If I reboot, oftentimes the squirrely errors disappear.

Go figure.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,247
Messages
5,443,325
Members
405,227
Latest member
Ankit Jain

This Week's Hot Topics

Top