Dear All,
I have a software package that dynamically updates a linked spreadsheet, sometimes several times a second.
My purpose is to capture the current data values of a given range once or twice a second, although the precise frequency doesn't have to be spot on, and carry out some calculations and record the output, along with the current date/time.
In an ideal world I would like to have used a worksheet_change event on the interactive spreadsheet, but the vendor has confirmed that this function will not work with the spreadsheet's interaction with its software.
So, I thought I would have a crack at having the interactive worksheet open and have a looping copy/paste to a second worksheet, and then have the worksheet_change event trigger within the second, non-interactive worksheet.
Whilst I have built VBA and made progress using a variety of examples nabbed from the vast resource of posts on this site, I am having trouble making the worksheet_change / worksheet_selectionchange trigger without having to actually click on a cell in the second, non-interactive worksheet.
It does the copy / paste and updates the values in the cells, but nothing happens until I click on a cell, and sometimes not all of the data is captured until the next change happens and I click again on a cell.
Enough babbling - here is my code:
WORKSHEET 1 of File 1
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SleepAndCopy()
Do
Call CopyPasteRange
Sleep 500 'this value is in milliseconds ie 1000 for 1 second
Loop
End Sub
Sub CopyPasteRange()
'Dim LstRow As String
Workbooks("200902021-1").Sheets("Interactive").Activate
Range("D6:I9").Copy
Workbooks("200902021-1").Sheets("DataCapture").Activate
LstRow = Application.Range("C6:H9")
ActiveCell.PasteSpecial (xlPasteValues)
End Sub
WORKSHEET2 of File 1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) = "E6" Then
Application.EnableEvents = False
Range("j" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("k" & Rows.Count).End(xlUp)(2).Value = Now
Range("l" & Rows.Count).End(xlUp)(2).Value = "=SUM(C7:E7)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "F6" Then
Application.EnableEvents = False
Range("m" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("n" & Rows.Count).End(xlUp)(2).Value = Now
Range("o" & Rows.Count).End(xlUp)(2).Value = "=SUM(F7:H7)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "E8" Then
Application.EnableEvents = False
Range("p" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("q" & Rows.Count).End(xlUp)(2).Value = Now
Range("r" & Rows.Count).End(xlUp)(2).Value = "=SUM(C9:E9)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "F8" Then
Application.EnableEvents = False
Range("s" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("t" & Rows.Count).End(xlUp)(2).Value = Now
Range("u" & Rows.Count).End(xlUp)(2).Value = "=SUM(F9:H9)"
Application.EnableEvents = True
End If
End Sub
What do I actually want to achieve - I want to copy the data values in range D6:I9, Sheet1 and paste them to range C6:H9 in Sheet2.
I then want Sheet2 to realise that the data values have changed, then copy the value from between 1 and 4 cells, perform a 3 cell sum on 1-4 ranges, and enter the NOW() value 1-4 times, dependant on which cells actually changed.
I think that is a good summary of what I am hoping to do, and if anyone can spot the errors of my ways, then advice will be gratefully received. If an example of input and current generated output is required, then that could be supplied to.
Thanks for your time.
I have a software package that dynamically updates a linked spreadsheet, sometimes several times a second.
My purpose is to capture the current data values of a given range once or twice a second, although the precise frequency doesn't have to be spot on, and carry out some calculations and record the output, along with the current date/time.
In an ideal world I would like to have used a worksheet_change event on the interactive spreadsheet, but the vendor has confirmed that this function will not work with the spreadsheet's interaction with its software.
So, I thought I would have a crack at having the interactive worksheet open and have a looping copy/paste to a second worksheet, and then have the worksheet_change event trigger within the second, non-interactive worksheet.
Whilst I have built VBA and made progress using a variety of examples nabbed from the vast resource of posts on this site, I am having trouble making the worksheet_change / worksheet_selectionchange trigger without having to actually click on a cell in the second, non-interactive worksheet.
It does the copy / paste and updates the values in the cells, but nothing happens until I click on a cell, and sometimes not all of the data is captured until the next change happens and I click again on a cell.
Enough babbling - here is my code:
WORKSHEET 1 of File 1
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SleepAndCopy()
Do
Call CopyPasteRange
Sleep 500 'this value is in milliseconds ie 1000 for 1 second
Loop
End Sub
Sub CopyPasteRange()
'Dim LstRow As String
Workbooks("200902021-1").Sheets("Interactive").Activate
Range("D6:I9").Copy
Workbooks("200902021-1").Sheets("DataCapture").Activate
LstRow = Application.Range("C6:H9")
ActiveCell.PasteSpecial (xlPasteValues)
End Sub
WORKSHEET2 of File 1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) = "E6" Then
Application.EnableEvents = False
Range("j" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("k" & Rows.Count).End(xlUp)(2).Value = Now
Range("l" & Rows.Count).End(xlUp)(2).Value = "=SUM(C7:E7)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "F6" Then
Application.EnableEvents = False
Range("m" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("n" & Rows.Count).End(xlUp)(2).Value = Now
Range("o" & Rows.Count).End(xlUp)(2).Value = "=SUM(F7:H7)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "E8" Then
Application.EnableEvents = False
Range("p" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("q" & Rows.Count).End(xlUp)(2).Value = Now
Range("r" & Rows.Count).End(xlUp)(2).Value = "=SUM(C9:E9)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "F8" Then
Application.EnableEvents = False
Range("s" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("t" & Rows.Count).End(xlUp)(2).Value = Now
Range("u" & Rows.Count).End(xlUp)(2).Value = "=SUM(F9:H9)"
Application.EnableEvents = True
End If
End Sub
What do I actually want to achieve - I want to copy the data values in range D6:I9, Sheet1 and paste them to range C6:H9 in Sheet2.
I then want Sheet2 to realise that the data values have changed, then copy the value from between 1 and 4 cells, perform a 3 cell sum on 1-4 ranges, and enter the NOW() value 1-4 times, dependant on which cells actually changed.
I think that is a good summary of what I am hoping to do, and if anyone can spot the errors of my ways, then advice will be gratefully received. If an example of input and current generated output is required, then that could be supplied to.
Thanks for your time.