Automatically copy cells value and paste into next available empty cell

7

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,
 
Sort by date Sort by votes
Bad copy on my part. With the code out of the change_event heading, Target does not play nice with the rest of the macro.

Try this fresh approach.

Put this change event macro in the sheet 1 code module.

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.OnTime Now + TimeValue("00:05:00"), "TheNameOfMySub"
End Sub

And this macro in a regular module, where you will go to the vb editor > click on Insert > click Module and paste Sub TheNameOfMySub() in it.

Code: 
Option Explicit

Sub TheNameOfMySub()

Dim c As Range
Application.ScreenUpdating = False
If Not Range("A1:A15") Is Nothing Then
  For Each c In Range("A1:A15")
    c.Copy
      If Sheets("Sheet1").Range("A" & c.Row).Value = "" Then
         Sheets("Sheet1").Range("A" & c.Row).PasteSpecial
        Else
          Sheets("Sheet2").Cells(c.Row, Sheets("Sheet2").Cells(c.Row, Columns.Count). _
          End(xlToLeft).Column + 1).PasteSpecial
      End If
  Next
       Else
          Exit Sub
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

For testing you can set the time to 10 seconds instead of five minutes, as you can see here.

Application.OnTime Now + TimeValue("00:00:10"), "TheNameOfMySub"


I am assuming you know that the code will take each cell in A1:A15 and paste it to the first empty cell of each row on sheet 2.
So the data that is copied is not a straight down the column paste in sheet 2 if there is data already in that row..

Let me know if this works for you or post back with any questions. About anything beyond this and I will probably need to consult the pros, but lets see if this is going in the right direction for you.

Regards,
Howard
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
L. Howard said:
Bad copy on my part. With the code out of the change_event heading, Target does not play nice with the rest of the macro.

Try this fresh approach.

Put this change event macro in the sheet 1 code module.

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.OnTime Now + TimeValue("00:05:00"), "TheNameOfMySub"
End Sub

And this macro in a regular module, where you will go to the vb editor > click on Insert > click Module and paste Sub TheNameOfMySub() in it.

Code: 
Option Explicit

Sub TheNameOfMySub()

Dim c As Range
Application.ScreenUpdating = False
If Not Range("A1:A15") Is Nothing Then
  For Each c In Range("A1:A15")
    c.Copy
      If Sheets("Sheet1").Range("A" & c.Row).Value = "" Then
         Sheets("Sheet1").Range("A" & c.Row).PasteSpecial
        Else
          Sheets("Sheet2").Cells(c.Row, Sheets("Sheet2").Cells(c.Row, Columns.Count). _
          End(xlToLeft).Column + 1).PasteSpecial
      End If
  Next
       Else
          Exit Sub
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

For testing you can set the time to 10 seconds instead of five minutes, as you can see here.

Application.OnTime Now + TimeValue("00:00:10"), "TheNameOfMySub"


I am assuming you know that the code will take each cell in A1:A15 and paste it to the first empty cell of each row on sheet 2.
So the data that is copied is not a straight down the column paste in sheet 2 if there is data already in that row..

Let me know if this works for you or post back with any questions. About anything beyond this and I will probably need to consult the pros, but lets see if this is going in the right direction for you.

Regards,
Howard
Click to expand...
.
Hi Howard,
Thanks for your update. I tried and seems like time thing is not working. If i manually run the code, it nicely copies data from sheet1 into sheet2 in column "B" only one time and includes the formula of reference cells from sheet1. Just need values not formula.
.
Regards,
 
Upvote 0
L. Howard said:
Bad copy on my part. With the code out of the change_event heading, Target does not play nice with the rest of the macro.

Try this fresh approach.

Put this change event macro in the sheet 1 code module.

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.OnTime Now + TimeValue("00:05:00"), "TheNameOfMySub"
End Sub

And this macro in a regular module, where you will go to the vb editor > click on Insert > click Module and paste Sub TheNameOfMySub() in it.

Code: 
Option Explicit

Sub TheNameOfMySub()

Dim c As Range
Application.ScreenUpdating = False
If Not Range("A1:A15") Is Nothing Then
  For Each c In Range("A1:A15")
    c.Copy
      If Sheets("Sheet1").Range("A" & c.Row).Value = "" Then
         Sheets("Sheet1").Range("A" & c.Row).PasteSpecial
        Else
          Sheets("Sheet2").Cells(c.Row, Sheets("Sheet2").Cells(c.Row, Columns.Count). _
          End(xlToLeft).Column + 1).PasteSpecial
      End If
  Next
       Else
          Exit Sub
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

For testing you can set the time to 10 seconds instead of five minutes, as you can see here.

Application.OnTime Now + TimeValue("00:00:10"), "TheNameOfMySub"


I am assuming you know that the code will take each cell in A1:A15 and paste it to the first empty cell of each row on sheet 2.
So the data that is copied is not a straight down the column paste in sheet 2 if there is data already in that row..

Let me know if this works for you or post back with any questions. About anything beyond this and I will probably need to consult the pros, but lets see if this is going in the right direction for you.

Regards,
Howard
Click to expand...
.
Hi Howard,
Thanks for your update. I tried and seems like time thing is not working. If i manually run the code, it nicely copies data from sheet1 into sheet2 in column "B" only one time and includes the formula of reference cells from sheet1. Just need values not formula.
.
Regards,
 
Upvote 0
Could you send me an example workbook to examine. Devoid of any sensitive data.

Include from start to finish any details of what is happening and where the info is coming from and what you expect as precisely as you can. I believe I have most of it but it would be nice to have it all in front of me along with the workbook. Only need the two sheets we've been talking about as far as I can tell.

I'm at...

l h kittle @ Comcast . net

Remove the spaces.

Howard
 
Upvote 0
Well, I asked a trusted pro for advice on how to make this work.

Got some code, some of which I understood, but was unable to make it preform consistently.

Major problems occur with the multitudes of changes that fire the event code each time a change is made and before the On Time macro time has elapsed. Had two sets of code, one for individual hand typed in changes and the other for en-mass (and often) changes.

I could not get the orchestra to preform as needed.

Sorry to say, I will have to recuses myself from further attempts.

Good luck, hope someone will be able to help you out.

Regards,
Howard
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Named ranges from multiple sections of cells copy paste not working
Replies
6
Views
144
Alex Blakenburg
A
K
  • Question
how to copy above cell values down
Replies
5
Views
118
kamranyd
K
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
283
B-radK
B
C
  • Question
VBA copy paste cells one by one
Replies
1
Views
70
Joe4
Joe4
WERNER SLABBERT
  • Question
copy and create problem
Replies
0
Views
186
WERNER SLABBERT
WERNER SLABBERT

Forum statistics

Threads
1,215,790
Messages
6,126,921
Members
449,348
Latest member
Rdeane

Share this page

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