Hello everyone,

I'm trying to get this Sub procedure to execute after the selected worksheet is printed. Any suggestions?

Sub Save_Printout()
Dim s As String, Sh As Worksheet

Set Sh = ActiveSheet
Sh.Copy
s = "c:\printed sheets\" & Range("M5").Value 'save location and name of worksheet
ActiveWorkbook.SaveAs Filename:=s
Range("A1:IV5000").Copy  'selects all cells
Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'pastes values 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

I just read my question, and I think it was a bit ambiguous. Let me restate my situation: I would like to save the worksheet after it is printed. I have the "saving part" of the code below, but 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. Maybe I should use some type of Do While or Do Until Loop or something?

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 Worksheets
  Set Sh = ActiveSheet 'sheet selected will be the sheet saved
    Sh.Copy
    s = "c:\Printed Worksheets\" & Range("M5").Value 'save location and title of worksheet
    ActiveWorkbook.SaveAs Filename:=s
    Range("A1:IV5000").Copy  'selects all cells
    Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'pastes values only
    Application.CutCopyMode = False
    Range("A1").Select 'takes cursor back to the top to make that sheet a litte "cleaner"
   ActiveWorkbook.Close SaveChanges:=True
End Sub

Edited 3 Years Ago by Dani: Formatting fixed

This article has been dead for over six months. Start a new discussion instead.