Automatically copy cells value and paste into next available empty cell

786javed

New Member
Joined
Jun 2, 2013
Messages
18
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,
 
Code:
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!
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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,

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


Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top