Copy,Paste and append row of data

rdstagg

New Member
Joined
Oct 29, 2003
Messages
44
This was a subject raised in 2002 - but no answer seems to have been found.

I use a DDE spreadsheet which adjusts share prices in real time.

I have managed to write a function in vb that beeps and turns cell w10 to true when a condition is met. (Sheet 1). Default is obviously false.

I would like to copy a row of data (row 11) when this happens and write it to sheet 2.

Each time the condition is met another row of data would be added to the same sheet .

This seems straightforward but I cannot work out how to do it
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,247
Hi rdstagg,
if your macro detects the condition that should trigger the copy, then you need to add to the existing code the instructions that perform the copy row11 and paste it at the bottom of the existing lines in sheet2.

Did I get the problem?

Also, your macro is intended to detect tyhe a new value imported via the dde link? If YES, did you use the method SetLinkOnData or a different approach?

Bye,
 

rdstagg

New Member
Joined
Oct 29, 2003
Messages
44
Yes Anthony

But its the copy and paste to a new sheet that I can' t do.
It sounds ridiculously easy (even to me) but I really have no skill/time at vba.
I don't know what setlinkondata is but my function merely compares one cell value to another.
I amended some code I found here - which was jolly useful.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,247
Hi rdstagg,
Sorry to be late, but I was not notified by mail of your answer.

You could use this macro:

Code:
Sub CopyRow()
Dest_WS = "List"       '<<<< Change as required
Source_WS = Range("A1").Worksheet.Name
Rows("11:11").Select
Selection.Copy
Sheets(Dest_WS).Select 
Range("A65536").End(xlUp).Offset(1, 0).Select 
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets(Source_WS).Select 
End Sub

This macro allows you to define which is the sheet to copy to (I used "List"), then collects the name of the starting sheet, selects and copy row 11, moves to the destination sheet, locates the first free line, pastes here row 11 contents, and returns to the starting sheet.

Open the VBA editor (<Alt>F11), open the “Module” that contain your current Macro, and paste there the instructions.
In this way, you will have a new macro (CopyRow) that needs to be started “manually”, or via a button or via a skortcut.

OR, you could add the new instruction to your existing macro (cutting Sub CopyRow() and End Sub, of course) so that when the condition will occour, after beeping and writing to W10, row 11 is copied to the destination sheet.

Setlinkondata is a method that can help in identifying that new data have been imported via the DDE link, to manage these changes in real time; I understand you used a different method, may be the OnTime for a periodical check.

If you need more instructions, please post.

Bye,
 

rdstagg

New Member
Joined
Oct 29, 2003
Messages
44

ADVERTISEMENT

Anthony47
Yes I am not getting notified either!
Thanks for this. My alarm is a function so when I cut and paste your code it doesn't seem to work - the alarm still goes off though.
Code:
Function Alarm2(cell, condition)
Dim wavfile As String
On Error GoTo errorhandler
If Evaluate(cell.Value & condition) Then

 Call sndPlaySound32("c:\Documents and Settings\Russell\My Sounds\ringin.wav", 1)


Alarm2 = True

Dest_WS = "Sheet2"       '<<<< Change as required
Source_WS = Range("A1").Worksheet.name
Rows("11:11").Select
Selection.Copy
Sheets(Dest_WS).Select
Range("A65536").End(xlUp).offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets(Source_WS).Select

    
Exit Function
End If
errorhandler:
Alarm2 = False

End Function
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,247
Hi rdstagg,
Unfortunately you cannot embed the move instructions in the code of a Function (UDF).

I did not understand the method you use to detect a change, in that the UDF is statically installed in a cell, and might be able to dtect "one" change but not 2, or 3, and more.

Anyway, at this point, I can suggest these ways:
1- A periodical macro, that is activated each N seconds or minutes via the OnTime method, that checks if the cell that contains the Udf is True; if Yes then the copy instructions will be executed.
But you should also clear that cell (the one that use the Udf) and prepare it for the next detection.

2- You forget about the Udf and use a the periodical macro to chech if the data changed, and if Yes you run the copy instructions.

3- Is the change that you wish to trap related to the dde imported data?
In this case let me know, as there is the "famous" Setlinkondata method that could be used to detect data variation in real time (vs the N seconds or minutes of latency of the OnTime method).

Bye,
 

rdstagg

New Member
Joined
Oct 29, 2003
Messages
44

ADVERTISEMENT

Hi Anthony47

The change is reflected by the changing values of the imported data in cell N11. Can you use this setlinkondata method?
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,247
Hi rdstagg,
Lets try the SetLinkOnData way.
I supposed that your sheet is named Sheet1, and cell C1 is available and we can use it as a flag to start/stop tracking the dde changes; if the sheet has a different name, or we must use another cell for the flag, then change the macro accordingly.

Right click on the tab with the sheet name, chose “Show code”; this will open the vba editor. Paste the following code into the right frame:


Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim FollowDDE As Integer 
If Target.Address <> "$C$1" Then Exit Sub 
Application.EnableEvents = False
    With ActiveWorkbook 
        FollowDDE = .Worksheets("Sheet1").Range("$C$1").Value 
        If FollowDDE >0 Then 
        .SetLinkOnData "IWDDE|STOCK_PRICE!'229947?contractPrice'", "CopyRow" 
        MsgBox "Tracking DDE changes has been started", vbOKOnly, "Info:"
        Else 
        .SetLinkOnData "IWDDE|STOCK_PRICE!'229947?contractPrice'", "" 
        MsgBox "Tracking DDE changes has been stopped", vbOKOnly, "Warning:" 
        End If 
End With 
Application.EnableEvents = True
End Sub

Modify if necessary the sheet name, the flag addres address now set at $C$1, and the dde link in the two SetLinkOnData instructions: you should put here the same link you have set in cell N11.

In a standard macro Module paste the following code:

Code:
Sub CopyRow() 
Dest_WS = "List"          '<<<< Change as required 
Source_WS = “Sheet1” ‘<<<<
Rows("11:11").Select 
Selection.Copy 
Sheets(Dest_WS).Select 
Range("A65536").End(xlUp).Offset(1, 0).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Sheets(Source_WS).Select 
End Sub

If you wish to set a timestamp on the copied row, you could use:
Code:
Sub CopyRow() 
Dest_WS = "List"          '<<<< Change as required 
Source_WS = “Sheet1” ‘<<<<
Sheets(Source_WS).Range("A11:E11").COPY   ‘<<<< Change as required
Sheets(Dest_WS).Select 
Range("A65536").End(xlUp).Offset(1, 0).Select 
ActiveCell.value = Now
Activecell.Offset(0,1).Select
ActiveSheet.Paste 
Application.CutCopyMode = False 
Sheets(Source_WS).Select 
End Sub

Have a look at the instructions marked "<<<<" and change the parametres according to your situation.

Instructions:
-set in C1 of the source sheet 0 (or empty) to mean “no tracking” or 1 to mean “track changes”; you should be informed by a Msgbox of these settings.
-if tracking is “on”, any change in the DDE cell should start the macro CopyRow
-CopyRow copies the information from the source sheet and appends it onto the “destination” sheet.


Please let us know if it helps.

Bye,
 

rdstagg

New Member
Joined
Oct 29, 2003
Messages
44
Hi

Forgive me but I am completely new to this! If I understand a little more I think I will get it.

"IWDDE|STOCK_PRICE!'229947?contractPrice'"

Can you tell me where this came from? I assume it is what I am trying to monitor (ie cell N11) ?
 

Forum statistics

Threads
1,141,847
Messages
5,708,946
Members
421,601
Latest member
Garlo

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
Top