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>
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>