More help on setting print headers via code

[KimberlyC]

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]

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?

 

[Dave Peterson]

Ps. If you qualify the ranges, you can drop the sh.acivate line. And the code
that keeps track of the activesheet, too.

 

[KimberlyC]

Hi Dave..
I added the .value to each range ...from your last posting..
Did I not do it correctly???
I'm confused now.

 

[KimberlyC]

Hi Dave

How do I qualify them?

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]

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.

Hi Dave

How do I qualify them?

 

[KimberlyC]

Thank you!!
That's make sense...

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]

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]

Do you have a ranges named "Insurance_Co", "Policyholder", "Policy_No.", "to",
and "From" on all those worksheets? (Sheet level names)

Or do you just have one range with each of those names in the whole workbook?
(workbook level names).

If you only have one range with that name, drop the sh. stuff from those range
references.

But if the names are workbook level names, then I'm not sure why you're
activating each of those sheets.

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!!

 

[Tom Ogilvy]

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

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!!

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.

And

 

[KimberlyC]

The names are at the workbook level..

Do you have a ranges named "Insurance_Co", "Policyholder", "Policy_No.", "to",
and "From" on all those worksheets? (Sheet level names)

Or do you just have one range with each of those names in the whole workbook?
(workbook level names).

If you only have one range with that name, drop the sh. stuff from those range
references.

But if the names are workbook level names, then I'm not sure why you're
activating each of those sheets.

 

[Dave Peterson]

So you have it working--without the sh.range() stuff????

The names are at the workbook level..

 

[KimberlyC]

Hi Dave..
Here's how I have it working..(code below)
I thought you could help me change it so it did not have to activate the
sheets as it went through the code.
The screen flickers alot while the code it running and I'm assuming it's due
to the activate sheet stuff.
The ranges in the code below are located on one worksheet in the workbook
(Acct Info)
Does this make sense???

Sub PrintHEADERALL()
'Set Print Header on all worksheets except Acct Info

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) Then
'do nothing
Else
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 & " Audit Period " & Range("From2").Value & " "
& Range("To2").Value
End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing

End Sub

 

[Dave Peterson]

If you just comment out (or delete them)

sh.activate
and
sh1.activate

does it work ok?

Hi Dave..
Here's how I have it working..(code below)
I thought you could help me change it so it did not have to activate the
sheets as it went through the code.
The screen flickers alot while the code it running and I'm assuming it's due
to the activate sheet stuff.
The ranges in the code below are located on one worksheet in the workbook
(Acct Info)
Does this make sense???

Sub PrintHEADERALL()
'Set Print Header on all worksheets except Acct Info

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) Then
'do nothing
Else
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 & " Audit Period " & Range("From2").Value & " "
& Range("To2").Value
End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing

End Sub

 

[Tom Ogilvy]

Just use
Application.ScreenUpdating = False
at the top
and
Application.SceenUpdating = True
at the end.

--
Regards,
Tom Ogilvy

Hi Dave..
Here's how I have it working..(code below)
I thought you could help me change it so it did not have to activate the
sheets as it went through the code.
The screen flickers alot while the code it running and I'm assuming it's due
to the activate sheet stuff.
The ranges in the code below are located on one worksheet in the workbook
(Acct Info)
Does this make sense???

Sub PrintHEADERALL()
'Set Print Header on all worksheets except Acct Info

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) Then
'do nothing
Else
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 & " Audit Period " & Range("From2").Value & " "
& Range("To2").Value
End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing

End Sub

 

[KimberlyC]

Just did that...and ...Yep!! That seems to work just fine!!!
Thank you!!!