Try this simple alteration of your original and see if it works. I did make
a slight adjustment in the string you have set up - you had a hyphen
floating around without quotes. I took it out, so you might need to adjust
the string (after you get this version working). In any event, this ran
fine for me:

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, "Acct Info", vbTextCompare) = 0 Then
sh.PageSetup.CenterHeader = _
"&""Arial,Bold""&11" & "Insurance Co: " _
& Range("Insurance_Co").Value & _
" Policyholder: " & _
Range("Policyholder").Value & Chr(10) _
& "Policy No: " & Range("Policy_No").Value _
& Chr(10) & "Policy Period " & _
Range("From").Value _
& " " & Range("To").Value

End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing

End Sub

If you have multiple acct info sheets, then Dave's question about how you
define these range names is certainly appropriate
--
Regards,
Tom Ogilvy

"KimberlyC" wrote in message
...
Hi Dave,
I tried this.. but I'm sure I did it wong
I get a Method Range of object Worksheet Failed
erorr.

Can you please show me how to alter the code below to qualify the ranges.
The sheet that has the ranges in it.. is called Acct Info.

Thanks!!
"Dave Peterson" wrote in message
...
You qualify them by telling your code what the range belongs to:

In other words, write them with a leading "sh.":

& sh.range("....").value ...

======
Since you didn't qualify them, you relied on the fact that since your
code
was
in a general module, an unqualified range refers to the active sheet.
That's
why your code had to have:

sh.activate

inside the loop.

When you qualify the range (include what sheet the range belongs to),
you
can
avoid the selecting (and you don't have to keep track of where you
started).


=====
The .value was added because I don't like to use the default property of
the
range. I like to explicitly use .value.

KimberlyC wrote:

Hi Dave

How do I qualify them?

"Dave Peterson" wrote in message
...
Ps. If you qualify the ranges, you can drop the sh.acivate line.
And
the
code
that keeps track of the activesheet, too.

Dave Peterson wrote:

if lcase(sh.name) like "acct info*" then
'do nothing
else
'do all the work
end if

instead of the InStr() line.

And what about those unqualified ranges?

Do you need sh.range("Insurance_Co").value?

KimberlyC wrote:

Hi!!
I'm using the code below to set the headers on all worksheets in
the
workbook that have the name Adjustments in it.
I need to change this code to set this same header (as below) to
all
worksheets in the active workbook except for any worksheets with
the
name
"Acct Info"
(There could be Acct Info (2), Acct Info (3) and so on...)
I'm not sure how to do this ....

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.CenterHeader = "&""Arial,Bold""&11" &
"Insurance
Co: "
& Range("Insurance_Co").Value & " " & "Policyholder: " &
Range("Policyholder").Value & Chr(10) & "Policy No: " &
Range("Policy_No").Value & Chr(10) & "Policy Period " &
Range("From").Value
& " " & Range("To").Value

End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing

End Sub

As always.. Thanks in advance for your help,
Kimberly

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson