Paste special Macro problem

[beeone]

Hello

I am using paste special to collect data from some data logging software using DDE.

The data appears in excel and updates live no problem (the paste special is set on my worksheet)

I then have a macro to manipulate the data but it doesnt 'notice' the live data changing it only works when I change data manually on the spread sheet.

I'm only just learning Vba/macros whilst doing this project and I'm guessing theres some thing I dont know about paste special data changing and macros.

Any help much appreciated

 

[texasalynn]

Welcome to MrExcel board....

post the code you have

 

[beeone]

Hi thanks for reply

I've returned to this project after a couple of months, when I opened my excel file to view the code all the macro code had gone.
This happend a few times when I was working on the project before but hasnt happend lately, so I have lost the code mentioned in first post.

The special paste mentioned in first post imports an incrementing data value in one cell .
I am then using three other cells to total the count per day, month and year.

I basically want the three cells to increment by 1 every time the special paste value increments and then when the day, month or year changes the relavent cell to reset to zero.

I can remember that no matter how I cahnged my code the three cells would only increment when I manually changed the special paste data.
The same happens with the below code which I'm trying to use to reset the values to zero, it actually resets them to zero as soon as I click on the cell without changing the value.

It seems that I can only get a maco to run on manual changes and nothing thats happening automatic on the sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Day change reset--------------------
If Target = Workbooks("test24.xlsx").Worksheets("sheet1").Range("B11").Value Then
Range("B19") = 0
End If
'Month change reset--------------------
If Target = Workbooks("test24.xlsx").Worksheets("sheet1").Range("C11").Value Then
Range("C19") = 0
End If
'Year change reset---------------------
If Target = Workbooks("test24.xlsx").Worksheets("sheet1").Range("D11").Value Then
Range("D19") = 0
End If

End Sub

Hope thats fairly clear.

thanks

 

[Norie]

Have you tried using the Change event instead of the SelectionChange event?

 

[beeone]

Have you tried using the Change event instead of the SelectionChange event?

Hi thanks Norie, that solved the values resetting when I click the cell, they only reset now when the value has been changed.

Still it will only works when I manually change the day/date, when the day/date changes automatically the code does not run/work.

 

[Norie]

Don't you want this code to be run when the values change?

 

[beeone]

Hi

yes but it only runs when I manually change the values not when they change automatically. using excel formulas

e.g for one cell i use the DAY() function, but when the value in it changes from say 17 to 18 the code does not run.

Is this because I'm using a formula and as far as the macro is aware the formula entered into cell has not changed?

Its the same with the paste special I'm using??

 

[Norie]

Yes, the problem is changes in results in formulas don't trigger the Change event.