Hi again,

Well, I just tried it by inserting that line at the end of my for/next loop
and found that:

1. it does send each of the worksheets to the printer as they get looped
through
2. however, each worksheet is printed separately and so the page number and
the total pages do not show what I expect when multiple worksheets are
selected
3. moreover, after the completion of the for/next loop and all worksheets
have been printed, the print popup window still pops up at the end

So my follow-up questions a
A) Is there a way to get the page numbers to reflect the multiple worksheet
selection?
B) Is there a way not to have the print popup window pop up at the end?
C) is what I'm trying to do even possible?

Thanks a lot.


"rockhammer" wrote:

Hi OssieMac, thanks for your suggestion. I will try that when I get in the
office tomorrow. I suppose this solution will print the page numbers and
total page numbers correctly when multiple worksheets are selected. Thanks
again.


"OssieMac" wrote:

Hi,

Insert the following code as the last line of your For each WS - Next code
and it will print each sheet as soon as it has set the footer.

WS.PrintOut Copies:=1, Collate:=True
Next WS

Regards,

OssieMac

"rockhammer" wrote:

Hello, I have the following code as before print macro in a worksheet where
all the worksheets that I would print ("meta" is one I don't print) have
identical column structure & page set up (just the rows of data differ):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Meta = "Meta"
destCell = Worksheets(Meta).Range("b43").Value
Dim WS As Worksheet
For Each WS In ActiveWindow.SelectedSheets
'MsgBox WS.Name & " ; " & WS.Range(destCell).Value
WS.PageSetup.CenterFooter = "&8data as of " _
& Format(WS.Range(destCell).Value, "yyyy-mm-dd hh:mm:ss") _
& Chr(13) & "Page &P of &N"
Next WS
End Sub

If I select just one worksheet to print, it prints with the footer data as
expected.
If I select multiple worksheets to print, this macro would go through all
the worksheets to update the footers as expected *but* after all the footers
are updated all the worksheets selected become de-selected except the first
one selected so that only the first worksheet gets printed.

Is there a way to modify the macro to make sure it will print all selected
tabs?

Thanks a lot.