Have a small task that I tried to solve with this macro:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
ActiveSheet.PageSetup.LeftFooter = "&A"
ActiveSheet.PrintOut
Next
End Sub

Not very complex: Cycle through all sheets,
for each and every sheet (the one active):
- set left footer to show the Worksheet tab ("&A")
- print it

Any good advise from all of You ?

Arild

How about:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wk As Worksheet
For Each wk In Worksheets
wk.PageSetup.LeftFooter = "&A"
Next
End Sub

You were cycling through all the worksheets, but only changing the activesheet.
And if you're in the _beforeprint routine, then it's gonna print unless you stop
it. So I got rid of that line.

Arild wrote:

Have a small task that I tried to solve with this macro:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
ActiveSheet.PageSetup.LeftFooter = "&A"
ActiveSheet.PrintOut
Next
End Sub

Not very complex: Cycle through all sheets,
for each and every sheet (the one active):
- set left footer to show the Worksheet tab ("&A")
- print it

Any good advise from all of You ?

Arild

--

Dave Peterson

Thanks a lot for raising those two major points of shortcoming in my coding
!

"Dave Peterson" wrote in message
...
How about:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wk As Worksheet
For Each wk In Worksheets
wk.PageSetup.LeftFooter = "&A"
Next
End Sub

You were cycling through all the worksheets, but only changing the
activesheet.
And if you're in the _beforeprint routine, then it's gonna print unless
you stop
it. So I got rid of that line.

Arild wrote:

Have a small task that I tried to solve with this macro:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
ActiveSheet.PageSetup.LeftFooter = "&A"
ActiveSheet.PrintOut
Next
End Sub

Not very complex: Cycle through all sheets,
for each and every sheet (the one active):
- set left footer to show the Worksheet tab ("&A")
- print it

Any good advise from all of You ?

Arild

--

Dave Peterson