Thread: Copy data from sheet1 to sheet2

How do I copy data from one sheet to another - the first sheet has a fixed range of data and I need to copy this to sheet2 so that data is added to the first blank row in sheet2. Also, how can I code this so that it does this periodically (every 30 seconds)?

Hi BigBlackMamba

What happens to the data on sheet1 after it's been copied to sheet2? Does it get erased? Does it stay on sheet1 and get marked as processed? If it gets marked, what column?

You indicate you want this process to run every 30 seconds. I don't know the source of the data for sheet1 and I don't know the consequences of running this procedure when sheet1 data is being updated. But, it can be done.

A sample of your data would be nice.

John

Hi BigBlackMamba

Put this code in the Workbook Module

Code:

Option Explicit
Private Sub Workbook_Open()
    StartTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
    Call StopTimer
End Sub

Put this code in a general module

Code:

Public RunTime
Option Explicit
Public Sub Copy_1_To_2()
    Dim LR As Long
    LR = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheet1.UsedRange.Copy Destination:=Sheet2.Range("A" & LR)
    StartTimer
End Sub
Sub StartTimer()
    RunTime = Now + #12:00:02 AM#
    Application.OnTime RunTime, "Copy_1_To_2", schedule:=True
End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime RunTime, "Copy_1_To_2", schedule:=False
End Sub

A sample book is attached for your perusal. The code is set to run every 2 seconds.

John

Hi BigBlackMamba

What you have may work well in a static world (sheet 1 is not being updated). I've no idea what happens when sheet 1 is being updated the same time the procedure runs on the timer. Guess we'll find out.

If your immediate issue is resolved, please mark your post as "solved". A click on the scales is appreciated if you feel it;s appropriate.

John