|
BeforePrint help
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
BeforePrint help
Put a loop around the header set up as follows.......... Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next --- Cheers Nigel "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
BeforePrint help
Sorry did not change your code to set the header on the specific
sheet........ Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Range("A1") = 1 Then wks.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&Pof " & N Else wks.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next -- Cheers Nigel "Nigel" wrote in message ... Put a loop around the header set up as follows.......... Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next --- Cheers Nigel "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
BeforePrint help
Thanks Nigel
I think this is beginning to make sense. Lou "Nigel" wrote: Sorry did not change your code to set the header on the specific sheet........ Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Range("A1") = 1 Then wks.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&Pof " & N Else wks.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next -- Cheers Nigel "Nigel" wrote in message ... Put a loop around the header set up as follows.......... Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next --- Cheers Nigel "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
BeforePrint help
Final:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Add Header Macro N = WorkSheets("Cover").Range("MyPages").Value Dim wks As Worksheet For Each wks In ActiveWorkbook.WorkSheets If wks.Range("A1") = 1 Then wks.PageSetup.RightHeader = "&""Arial Narrow,Regular"_ "&8Page &P of " & N Else wks.PageSetup.RightHeader = "" End If Next End Sub It works PERFECTLY ... WOOHOO!!! Thanks again to both you and Tom Health and Happiness Lou "Rookie 1st class" wrote: Thanks Nigel I think this is beginning to make sense. Lou "Nigel" wrote: Sorry did not change your code to set the header on the specific sheet........ Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Range("A1") = 1 Then wks.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&Pof " & N Else wks.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next -- Cheers Nigel "Nigel" wrote in message ... Put a loop around the header set up as follows.......... Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next --- Cheers Nigel "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
| All times are GMT +1. The time now is 03:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com