Snasui Excel Forum

Code: Select all

Option Explicit
Dim CountDown As Date

Sub Timer()
    CountDown = Now + TimeValue("00:05:00")
    Application.OnTime CountDown, "Reset"
End Sub

Sub Reset()
Dim count As Date
count = TimeValue("0:05:00")
On Error Resume Next
count = count - TimeValue("0:05:00")
    If count <= 0 Then
        Worksheets("Sheet1").Range("A9").QueryTable.Refresh BackgroundQuery:=False
        PasteData
    End If
Call Timer
End Sub

Sub DisableTimer()
On Error Resume Next
    Application.OnTime EarliestTime:=CountDown, Procedure:="Reset", Schedule:=False
End Sub

Sub PasteData()
Dim rs As Range
Dim rt As Range
With Worksheets("Sheet1")
Set rs = .Range("N9", .Range("N" & Rows.count).End(xlUp)) _
    .Offset(0, -13).Resize(, 14)
End With
Set rt = Worksheets("Sheet2").Range("N" & Rows.count).End(xlUp).Offset(1, -13)
rs.Copy rt.Paste
Application.CutCopyMode = False
End Sub

Code: Select all

Sheets("Sheet1").Range("A1").Select
Sheets("Sheet2").Range("A1").Paste

Code: Select all

Workbooks("Book1.xls").Sheets("Sheet1").Range("A1").Copy
Workbooks("Book1.xls").Sheets("Sheet2").Range("A1").Paste

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DisableTimer
End Sub

Private Sub Workbook_Open()
    Timer
End Sub