Hi rockhammer,

Sorry the last suggestion did not do what you wanted. However, I have done
some more testing with the code below. It does not deselect the sheets unless
I force it to with the test line of selecting sheet (1). But anyway I have
come up with a possible solution to save the selected sheets and then
re-select them. Have a look at it and test it and see what it does. Note the
comments.

Also make sure that you are not calling another event when you get the date
and time because that is the only real difference and I don't know what you
have in that cell.


Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Dim WS As Worksheet
Dim wsSel 'Don't know what to declare as

Set wsSel = ActiveWindow.SelectedSheets

For Each WS In ActiveWindow.SelectedSheets
'Note:- Next code line uses 'Now' for date and time
WS.PageSetup.CenterFooter = "&8data as of " _
& Format(Now, "yyyy-mm-dd hh:mm:ss") _
& Chr(13) & "Page &P of &N"
Next WS
Stop 'Test. Check what sheets are selected
Sheets(1).Select 'Testing only
Stop 'Test. Check only first sheet is selected
wsSel.Select
Stop 'Test. Check if all required sheets are selected
Cancel = True 'Cancel print during testing.
End Sub

Best of luck and let me know if it works (or not).

Regards,

OssieMac


"rockhammer" wrote:

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.