Hi everyone :-)
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
'location and names of workbooks
s = "c:\Printed Worksheets\" & Range ("M5").Value
'selects all cells
ActiveWorkbook.SaveAs Filename:=s Range("A1:IV5000").Copy
'pastes values & number formats only
Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'this is just to take the cursor back to the top to make that sheet a
litte "cleaner"
Application.CutCopyMode = False Range("A1").Select
ActiveWorkbook.Close
SaveChanges:=True
End Sub
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
