BeforePrint help

[Guest]

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

 

[Nigel]

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

 

[Nigel]

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

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

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

 

[Guest]

Thanks Nigel
I think this is beginning to make sense.
Lou

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

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

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

 

[Guest]

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

Thanks Nigel
I think this is beginning to make sense.
Lou

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

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

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