Setting printer header via code

[KimberlyC]

Hi
I'm running this code below to set the left header on each worksheet in my
activeworkbook with name "Adjustments".
It's working good..but is there a way to set the header to line up with the
left margin?
I have my left and right margins set to 0

Sub PrintHEADERAdjustment()
'Set Print Header on Adjusmtents sheets

Dim sh1 As Excel.Worksheet
Dim sh As Excel.Worksheet
Set sh1 = ActiveWorkbook.ActiveSheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If InStr(1, sh.Name, "Adjustments", vbTextCompare) Then
sh.PageSetup.LeftHeader = "&""Arial,Bold""&11" & "Insurance Co: " &
Range("Insurance_Co") & " " & "Policyholder: " & Range("Policyholder") &
Chr(10) & "Policy No: " & Range("Policy_No") & " " & " Period " &
Range("From2") & " " & Range("To2")

End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing

End Sub

Thanks in advance!
Kimberly

 

[Sean Connolly]

Hi Kimberly,

Sorry, don't have an answer for you, but I'd be very interested if one of
the other "gurus" in this forum does.

This has annoyed me for a number of years as regardless of your Page Setup
settings, the position of the header/footer seems to be fixed by Excel. i.e.
0.75" in from the left and 0.75" in from the right.

Regards, Sean.

 

[JE McGimpsey]

You can't adjust header/footer margins. If you want to increase the
apparent margin, you can use space characters (using a fixed font like
Courier, you could even calculate the exact margin width).

 

[Dave Peterson]

I think the only way you can align headers is by adding filler characters (and
it doesn't usually match up perfect (for me anyway)).

Leading spaces are easy, but for trailing spaces, the last character can be the
HTML non-breaking space hit and hold the alt-key while typing 0160 on the
numeric keypad.

(Excel likes to just chop regular trailing spaces.)

===========

Ps. if those ranges (range("policyholder") are sheet level names, maybe you
should qualify them:

sh.range("policyholder").value

(I like .value, too!)

 

[KimberlyC]

Thank you all for your help.. I'll just center the headers and move on !