Automatically copy cells value and paste into next available empty cell

[786javed]

Hi,
Cell range in Sheet1 -- A1 : A15 updates value with forex rate every minute, linked with a DDE external data link.
.
Need to copy each cell value and paste into Sheet2 rows starting from A1:A15 next available empty cell every 5 minutes.
.
Example:
Sheet1/A1 value should be copy/paste to Sheet2/A1,B1,C1,D1 .... continue same row with 5 minutes (time interval may be change)
Sheet1/A2 value should be copy/paste to Sheet2/A1,B2,C2,D1 .... continue same row with time interval define.
.
Any macro for excel 2007 will be great help.
.
Regards,

 

[MARK858]

Try the codes below. Run the macro "test" (change the 3 seconds to suit)

Sub test()
    Dim dTime As Date
    dTime = Now + TimeValue("00:00:03")
    Application.OnTime dTime, "CopyIT"
End Sub

Sub CopyIT()
Dim i As Long
    Sheets("Sheet1").Range("A1:A15").Copy
    If Sheets("Sheet2").Cells(1, 1) = "" Then
        Sheets("Sheet2").Cells(1, 1).PasteSpecial
    Else
        i = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column + 1
        Sheets("Sheet2").Cells(1, i).PasteSpecial
    End If
    test
End Sub

 

[786javed]

Hi Mark,
.
Thanks for your help. Macro works fine copying data in Sheet2:A1 only and then it gives error:
.
"Macro may not be available in this workbook or Macros may be disabled".
.
If i run the macro again manually it copies into next available cells in Sheet2:B2 and then same error.
.
I checked and seems like macros are enabled.
.
Any suggestion.

Thanks,

 

[MARK858]

Where did you put the macros? Put them both in the same regular module

 

[786javed]

oopss.. my part of error. its working good now... .........

how can i stop it ?
.
I see now it copying data first time it picked up from Sheet:A1, if cell value changes its not picking up the changed value.
.
thanks,

 

[MARK858]

Ctrl - Break or in the VBE editor click Run then Break. Then reset.

But really you should put a limit to how many times you want it to run but you haven't specified how/when you want it to stop

 

[786javed]

you are right, i wanted to start capturing data from 5:30 pm and stop at 4:00 starting from Sunday to ending Friday every day. market closes between 4:00 to 5:30 pm.
.
I can change the time gap manually to my need, which may be every hour or 30 minutes.
.
You been great help..
.

 

[786javed]

I see now it copying data first time it picked up from Sheet:A1, if cell value changes its not picking up the changed value.
.
thanks,

 

[786javed]

The reason its copying same data is that its copying the formula from sheet1:A1:A15, which leads to same data.
It shouldn't copy the formula, just need to copy the value of the cells.
.
thanks,

 

[MARK858]

Sub CopyIT()
Dim i As Long
    Sheets("Sheet1").Range("A1:A15").Copy
    If Sheets("Sheet2").Cells(1, 1) = "" Then
        Sheets("Sheet2").Cells(1, 1).PasteSpecial [COLOR=#ff0000]Paste:=xlPasteValues[/COLOR]
    Else
        i = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column + 1
        Sheets("Sheet2").Cells(1, i).PasteSpecial [COLOR=#ff0000]Paste:=xlPasteValues[/COLOR]
    End If
    test
End Sub

 

[786javed]

Mark,

worked perfect. Appreciate your help.