Calling Macros based on cell value

[Jriggle]

Hello, I am still trying to learn the very basics of VBA. I have put a few copy and paste coding schemes together to make a few things work in the past be I am becoming stumped on this one and I am ready for some help.

I am taking information from a plc and using software called OSISOFT with a excel add on called PI Datalink. I am not sure if this matters or not but I figured I would let you know. Once the information comes in it refreshes automatically every 5 seconds. I have a cell set up C5 that has the value of the tag. I tried doing a VLOOKUP in the spreadsheet to make the B6 my trigger point but that didn't work either. I cannot make any of my VBA codes recognize the value of the cell (B5 or C5), I am assuming because it has a formula involved. What I need to happen is when C5 is equal to 25 copy row 2 of sheet 1 and paste only the values to sheet 2 row 2 next available row. I have a little bit of this already but I cannot make these functions work automatically because my initial "trigger" will not work.

ThisWorkBook in VBA
Private Sub worksheet_change(ByVal target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("B6")
If Application.Intersect(KeyCells, Range(target.Address)) = 25 Then
' Call Calculate_range
Call CopyStuff
End If
End Sub



Module 1 In VBA

Sub Calculate_range()
Rows("2:2").Calculate
Application.OnTime DateAdd("s", 5, Now), "Calculate_range"
End Sub

Sub CopyStuff()
Sheets("Sheet1").Rows("2:2").Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub


<strike></strike>Thank you for all of your help!! talk to you soon

<strike></strike>

 

[James006]

Hello,

Not sure to fully understand your requirement ....

But it seems you are using an external automatic add-on ...

So, in terms of event macros ... you should be testing Worksheet_Calculate()

Hope this will help

 

[Jriggle]

Yes, the data come from an outside source. I have the formula link in cell C5, I need to copy the values only of 2nd row of sheet 1 when the value of C5 is equal to 25. The calculations are supposed to update every 5 seconds but incase that fails is there a calculation "refresh all" we can do in VBA?