macros to save activesheet

[wlxxiii]

hi all,

i'm attempting to write a macros which will enable me to do the following:

at a preset interval, say every 10 minutes, the macros will save the activesheet as an excel file in a directory. what is different from other problems is that I need this activesheet to be saved "as values" only.

i have actually succeeded in saving the file every 10 minutes, but am only unsuccessful with the save "as values" portion. any advice?

Thanks!

 

[wigi]

Hello

Please post your code (within code tags) and then helpers will surely jump in and continue with the code.

 

[wlxxiii]

http://www.ozgrid.com/Excel/run-macro-on-time.htm

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Application.OnTime dTime, "MyMacro", , False
End Sub
Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue("01:00:00"), "MyMacro"
End Sub

Public dTime As Date
Sub MyMacro()
 
dTime = Now + TimeValue("01:00:00")
Application.OnTime dTime, "MyMacro"
 
 
Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = "P:\Attachments"
Const stSubject As String = "Hourly Report"
Const vaMsg As Variant = ""
Const vaCopyTo As Variant = ""
 
 
  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
  Dim stAttachment As String
 
 
 
 
  'Copy the active sheet to a new temporarily workbook.
  [COLOR=red]ActiveSheet.Cells.Select[/COLOR]
[COLOR=red] Selection.Copy[/COLOR]
[COLOR=red]   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=red]       :=False, Transpose:=False[/COLOR]
    
 
 
  stAttachment = stPath & "\" & Cells(1, 4).Value & ".xls"
 
  'Save and close the temporarily workbook.
  With ActiveWorkbook
    .SaveAs stAttachment
   ' .Close
  End With
 
 
 
End Sub

http://www.rondebruin.nl/notes7.htm

Initially I wanted to make Lotus send a report every hour so I combined rondebruin's code. Now I changed my mind about sending the email but save in P:\Attachments. The part highlighted in red doesnt seem to execute the paste values