|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
More help on setting print headers via code
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. KimberlyC wrote: 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 -- Dave Peterson |
More help on setting print headers via code
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 |
More help on setting print headers via code
The names are at the workbook level..
"Dave Peterson" wrote in message ... 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. KimberlyC wrote: 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 -- Dave Peterson |
More help on setting print headers via code
So you have it working--without the sh.range() stuff????
KimberlyC wrote: The names are at the workbook level.. "Dave Peterson" wrote in message ... 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. KimberlyC wrote: 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 -- Dave Peterson -- Dave Peterson |
More help on setting print headers via code
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" wrote in message ... So you have it working--without the sh.range() stuff???? KimberlyC wrote: The names are at the workbook level.. "Dave Peterson" wrote in message ... 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. KimberlyC wrote: 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 -- Dave Peterson -- Dave Peterson |
More help on setting print headers via code
If you just comment out (or delete them)
sh.activate and sh1.activate does it work ok? KimberlyC wrote: 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" wrote in message ... So you have it working--without the sh.range() stuff???? KimberlyC wrote: The names are at the workbook level.. "Dave Peterson" wrote in message ... 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. KimberlyC wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
More help on setting print headers via code
Just use
Application.ScreenUpdating = False at the top and Application.SceenUpdating = True at the end. -- Regards, Tom Ogilvy "KimberlyC" wrote in message ... 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" wrote in message ... So you have it working--without the sh.range() stuff???? KimberlyC wrote: The names are at the workbook level.. "Dave Peterson" wrote in message ... 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. KimberlyC wrote: 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 -- Dave Peterson -- Dave Peterson |
More help on setting print headers via code
Just did that...and ...Yep!! That seems to work just fine!!!
Thank you!!! "Dave Peterson" wrote in message ... If you just comment out (or delete them) sh.activate and sh1.activate does it work ok? KimberlyC wrote: 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" wrote in message ... So you have it working--without the sh.range() stuff???? KimberlyC wrote: The names are at the workbook level.. "Dave Peterson" wrote in message ... 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. KimberlyC wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 10:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com