Snasui Excel Forum

Code: Select all

Private Sub Workbook_Open()
  alerttime = Now + TimeValue("00:00:10")
  Application.OnTime alerttime, "EventMacro"
End Sub

Code: Select all

Public Sub EvenMacro()

ActiveWorkbook.Save

alerttime = Now + TimeValue("00:00:10")
Application.OnTime alerttime, "EventMacro"

End Sub

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:10"), "EventMacro", , False

    'ThisWorkbook.Close
End Sub

Code: Select all

'Code in Thisworkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Save
    Call StopTimer
End Sub

Private Sub Workbook_Open()
    Call EventMacro
End Sub

'Code in Module1
Dim t As Date
Public Sub EventMacro()
    On Error Resume Next
    t = Now + TimeValue("0:00:10")
    Application.OnTime t, "NextSave"
End Sub

Public Sub NextSave()
    On Error Resume Next
    ThisWorkbook.Save
    Call EventMacro
End Sub

Public Sub StopTimer()
    On Error Resume Next
    Application.OnTime t, "NextSave", , False
End Sub

Code: Select all

Workbooks.Open Filename:="\\Account\Data (D)\Book1.xlsm"
         ThisWorkbook.Activate
         Application.Goto Reference:="OFFSET(R10C30,0,2,1,4)"
         Selection.Copy
         Workbooks("Book1.xlsm").Activate
         Sheets("Sheet1").Select
         Application.Goto Reference:="OFFSET(R3C1,0,1,1,1)"
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
         Application.CutCopyMode = False
      
        ThisWorkbook.Activate
        Application.Goto Reference:="OFFSET(R10C30,0,10,1,1)"
        Selection.Copy
        Workbooks("Book1.xlsm").Activate
        Sheets("Sheet1").Select
        Application.Goto Reference:="OFFSET(R3C1,0,5,1,1)"
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("H3").Select
        Selection.ClearContents
        Workbooks("Book1.xlsm").Save
        Workbooks("Book1.xlsm").Close