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,

 

[786javed]

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

.
Hello,
can anyone help me above code which works fine, Thx to Mark.
.
Just need to copy multiple Cell Range from Sheet1 and past into blocks of cell range in Sheet2.
Example:
.
copy from sheet1/cell A14:A24 and past into sheet2/cell B14:B24
copy from sheet1/cell A18:A31 and past into sheet2/cell B28:B41
copy from sheet1/cell A6:A12 and past into sheet2/cell B16:B22
.
Any help will be highly appreciated. You guys are great!

 

[L. Howard]

Your third copy pastes over the first copy.

Typo?

Regards,
Howard

 

[786javed]

Howard,

Sorry, that was typo. Let me try again.
.
copy from sheet1/cell A14:A24 and past into sheet2/cell B14:B24
copy from sheet1/cell A18:A31 and past into sheet2/cell B28:B41
copy from sheet1/cell A6:A12 and past into sheet2/cell B46:B52
.
whole idea is to copy any range or single cell value from Sheet1 and past into any cell or range into Sheet2.
.
Hope this helps.
.
Regards,

 

[L. Howard]

Give this a try. You can probably see what adjustments you need to make for the destination, if any.

Option Explicit

Sub CopyIT()
    'copy from sheet1/cell A14:A24 and past into sheet2/cell B14:B24
    Sheets("Sheet1").Range("A14:A24").Copy
        Sheets("Sheet2").Cells(14, 2).PasteSpecial Paste:=xlPasteValues
        
   'copy from sheet1/cell A18:A31 and past into sheet2/cell B28:B41
    Sheets("Sheet1").Range("A18:A31").Copy
        Sheets("Sheet2").Cells(28, 2).PasteSpecial Paste:=xlPasteValues
        
    'copy from sheet1/cell A6:A12 and past into sheet2/cell B46:B52
    Sheets("Sheet1").Range("A6:A12").Copy
        Sheets("Sheet2").Cells(46, 2).PasteSpecial Paste:=xlPasteValues
        
End Sub

Regards,
Howard

 

[786javed]

Howard,
.
Great. Works perfect.

You guys are great!!