1

My problem is as follows:

I am running a real-time data server with a count-down from 900 to 0. Once the countdown hits 5 i want excel to copy range(B2 to B61) in sheet(RTD_NEWS) and paste it into a new worksheet as values.

The problem is that my macro will not do this automatically when the timeremaining hits 5. If i hit run when the cell is 5 it runs correctly.

I have made 2 macros, where the first needs to be run to do as i want and the second works if i change the cell manually but not with the RTD-links.

The first macro is:

Function Test()
Dim TimeRemaining As Long
TimeRemaining = ActiveWorkbook.Sheets("RTD_NEWS").Range("D2")
If TimeRemaining = 5 Then
Application.Goto ActiveWorkbook.Sheets("RTD_NEWS").Range("B2", "B61")
Selection.Copy
Worksheets.Add
Application.Goto ActiveSheet.Range("B21")
ActiveCell.PasteSpecial (xlPasteValues)
Application.Wait Now + TimeValue("00:00:06")
End If
End Function

The second macro is:

Sub auto_open()
   ' Run the macro DidCellsChange any time a entry is made in a
   ' cell in Sheet1.
   ThisWorkbook.Worksheets("RTD_NEWS").OnEntry = "DidCellsChange"
End Sub
Sub DidCellsChange()
  Dim KeyCells As String
   ' Define which cells should trigger the KeyCellsChanged macro.
   KeyCells = "D2"
   ' If the Activecell is one of the key cells, call the
   ' KeyCellsChanged macro.
   If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
   Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged()
   Dim Cell As Object

    For Each Cell In ActiveWorkbook.Sheets("RTD_NEWS").Range("D2")
   If Cell = "200" Then

Application.Goto ActiveWorkbook.Sheets("RTD_NEWS").Range("B2", "B61")
Selection.Copy
Worksheets.Add
Application.Goto ActiveSheet.Range("B21")
ActiveCell.PasteSpecial (xlPasteValues)
Application.Wait Now + TimeValue("00:00:06")
   End If
   Next Cell
End Sub
user2042289
  • 11
  • 2
  • Hi, Why not using `worksheet_change` event for this purpose? You can monitor the value in D2 using event listener. Some possible problem of your macro, maybe the time remaining passed 5 seconds , e.g. it drops below 5. (As you check once 6 seconds) When RTP updates, it's not the ActiveCell – Larry Feb 05 '13 at 09:13
  • 1
    Thanks, got it working using calculate. couldn't post my answer until 6 hours has passed. – user2042289 Feb 05 '13 at 10:06
  • do you mean worksheet_calculate event? Glad that you got it working. You can post your answer below and accept it or close this question. – Larry Feb 05 '13 at 10:08
  • Yeah, that's what i used, I will post my solution once the time limit is due. – user2042289 Feb 05 '13 at 10:10
  • I saw your code still contains `Application.Wait` , i don't think it's needed? Also, to avoid falling into infinite loop, you may want to see this post http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 on how to use `Application.enableEvents` – Larry Feb 05 '13 at 10:12

0 Answers0