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" wrote:
> 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 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.
> >
> >
> > "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.
> > > > >
|
Similar Threads
