Save worksheet after printed

[Mason]

I would like to save the worksheet after it is printed. I have the
"saving part" of the code below. I can't figure out how to get it to
execute after the worksheet is printed. I've been researching
different types of Methods (Events), but can't seem to identify any
that will run my Sub Procedure AFTER the worksheet is printed out.
How
can I get my code to REACT to a PrintOut Event (Method)? I've tried
conditionals like IF Worksheet.PrintOut THEN . . . but it doesn't
seem to work.


Maybe I should use some type of Do While, or Do Until Loop, or
something? What is wrong with my approach?Does anyone have any
suggestions?


Here's the code I have written which saves the worksheets:


Sub Save_Printout()


Dim s As String, Sh As Worksheet
Set Sh = ActiveSheet
Sh.Copy
s = "c:\Printed Worksheets\" & Range ("M5").Value 'location and names
of workbooks
ActiveWorkbook.SaveAs Filename:=s Range("A1:IV5000").Copy 'selects all
cells
Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'pastes
values & number formats only
Application.CutCopyMode = False Range("A1").Select 'this is just to
take the cursor back to the top to make that sheet a
litte "cleaner"
ActiveWorkbook.Close
SaveChanges:=True


End Sub

 

[Doug Glancy]

Mason,

I'm not sure if this will work, but you could use the BeforePrint event and
inside it cancel the print job that triggered the event, then do the
printing from inside the event and then call your saving sub. I think
getting it to print correctly may be difficult, but I don't know. Here's a
stab at it:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'turn off events so the "ThisWorkbook.Printout" command
'below won't start an endless loop of BeforePrint events
Application.EnableEvents = False
'cancel the printing that triggered this event
Cancel = True
'start our own printing
ThisWorkbook.PrintOut
Call Save_Printout()
'turn events back on
'Application.EnableEvents = True
End Sub

hth,

Doug

 

[Mason]

Mason,

I'm not sure if this will work, but you could use the BeforePrint event  and
inside it cancel the print job that triggered the event, then do the
printing from inside the event and then call your saving sub.  I think
getting it to print correctly may be difficult, but I don't know.  Here's a
stab at it:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'turn off events so the "ThisWorkbook.Printout" command
'below won't start an endless loop of BeforePrint events
Application.EnableEvents = False
'cancel the printing that triggered this event
Cancel = True
'start our own printing
ThisWorkbook.PrintOut
Call Save_Printout()
'turn events back on
'Application.EnableEvents = True
End Sub

hth,

Doug











- Show quoted text -

Thanks for the suggestion Doug. This is a REALLY interesting and
innovative approach. I can't seem to get the private procedure to
only print the active worksheet. The private sub want's to print ALL
SHEETS. Any suggestions on how to get it to just print the active
(selected) worksheet?

-mason

 

[Doug Glancy]

Mason,

I'm not sure if this will work, but you could use the BeforePrint event
and
inside it cancel the print job that triggered the event, then do the
printing from inside the event and then call your saving sub. I think
getting it to print correctly may be difficult, but I don't know. Here's a
stab at it:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'turn off events so the "ThisWorkbook.Printout" command
'below won't start an endless loop of BeforePrint events
Application.EnableEvents = False
'cancel the printing that triggered this event
Cancel = True
'start our own printing
ThisWorkbook.PrintOut
Call Save_Printout()
'turn events back on
'Application.EnableEvents = True
End Sub

hth,

Doug











- Show quoted text -

Thanks for the suggestion Doug. This is a REALLY interesting and
innovative approach. I can't seem to get the private procedure to
only print the active worksheet. The private sub want's to print ALL
SHEETS. Any suggestions on how to get it to just print the active
(selected) worksheet?

-mason

Mason,

Glad you like that idea.

You should start by just recording a macro that prints the way you want.
I'm not much of an expert on printing via VBA, so if that doesn't help you
might want to start a new post.

Doug