| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help on setting print headers via code
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More help on setting print headers via code
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More help on setting print headers via code
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More help on setting print headers via code
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More help on setting print headers via code
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More help on setting print headers via code
Thank you!!
That's make sense... "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More help on setting print headers via code
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More help on setting print headers via code
Hi Dave..
I added the .value to each range ...from your last posting.. Did I not do it correctly??? I'm confused now. "Dave Peterson" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Setting up Default Headers/Footers in Excel | Excel Discussion (Misc queries) | |||
| setting up common headers | New Users to Excel | |||
| Setting more parameters with Print area code | Excel Programming | |||
| Setting sheet names as headers with page numbering | Excel Programming | |||
Hybrid Mode
