before print macro not printing multiple selected worksheets

[rockhammer]

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.

 

[OssieMac]

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]

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.

 

[rockhammer]

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 are:
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.

 

[OssieMac]

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]

SUCCESS!!! Thank you very much, OssieMac!

Your trick of storing the selected worksheets and then reselecting them just
before "end sub" solved my problem. I am attaching the code below so you can
see what it is like.

But to get back to you on your quesiton...The WS.Range(destCell).Value bit
(which you replaced with Now) just goes to the cell represented by destCell
(which is the same cell in each worksheet) which stores by value the time the
(externally sourced) data on each worksheet was refreshed. In other words,
the whole reason for me to get into this beforeprint macro business was just
to show the time each worksheet was actually refreshed. Little did I know it
was not that straightforward.

Anyhow, your efforts are much appreciated. I was about to abandon
beforeprint completely and go the different direction of a print macro
attached to a button instead (see my rant under "Print all (except one or
two)").

My code below. I'm not sure what Wb was for but it seemed to have worked
without it.

++++++++++++++++++++++++++++++
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Meta = "Meta"
destCell = Worksheets(Meta).Range("b43").Value
Dim WS As Worksheet

' 070329 - courtesy OssieMac in microsoft.public.excel.programming
' Problem: If multiple worksheets are selected to print, beforeprint macro
deselects all of them
' except the the first worksheet before it spools to the printer so that
just the first
' worksheet gets printed
' Solution: Record which worksheets are selected before editing the
headers/footers and then
' re-select them as the last step before "end sub"
Set wsSel = ActiveWindow.SelectedSheets

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

' 070329 - courtesy OssieMac in microsoft.public.excel.programming
' Re-select the worksheet(s) the user selected before sending to printer
wsSel.Select

End Sub
++++++++++++++++++++++++++++++++

 

[OssieMac]

O.K. Rockhammer; Another lesson.

Wb is the reference to the workbook which was activated at the time of
selecting Print. it is carried forward so that it can be used in your code.
Now when you have Application type events, the event will be called if you
have the workbook open and then open another workbook and decide to print
something from the other workbook. This could cause you real problems if you
do not address the situation in the code.
The following code is a demonstration to show what happens and how to handle
the situation with an if statement. You probably won't need the Else; I only
put that in for demo purposes.

Amend your code in your workbook and then create another workbook and print
from it and observe what happens and I am sure you will work out what I mean.

Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Dim WS As Worksheet
Dim wsSel

Cancel = True 'Cancel print during testing.

MsgBox "app_WorkbookBeforePrint called from " & Wb.Name

If Wb.Name = "Test Multi Sheet Print.xls" Then

MsgBox "Correct workbook. Therefore process"

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
wsSel.Select
Else
MsgBox "Processing skipped for workbook " & Wb.Name
End If

End Sub

Regards,

OssieMac