I am new to writing macros in Excel and have no VBA experience or training.
I have created a macro to copy data from one worksheet, where data is updated regularly via DDE, to another worksheet where the data is copied as inserted values. So over a day, I have a cumulative list of data retrieved from the DDE source. I have structured the macro as follows:
This appears to work fine for a single file. I want to do this for multiple groups of data. So I have used "save as" on the first file to create a number of files with similar format and the same macro names. I have simply changed the DDE formulas to retrieve a different set of data. When running multiple files, I get spurious results. The macros seem to get confused with each other. Some run OK, others don't update at the correct interval (i.e. every 2 mins). Further, it would appear that my live interaction with the worksheet can make the macro work. i.e. when I toggle to the workbook I can see that the macro has not updated for a period of time (say 1 hour), but it then updates as I am looking at the screen.
Should I be using different names for the macros in the different workbooks? Or is there another way to keep multiple macros in different workbooks separate from each other?
I have created a macro to copy data from one worksheet, where data is updated regularly via DDE, to another worksheet where the data is copied as inserted values. So over a day, I have a cumulative list of data retrieved from the DDE source. I have structured the macro as follows:
Sub CopyOver
...copy and paste data
Call Schedule
End Sub
Sub Schedule
TimeToCopy = Now + TimeValue("00:02:00") 'every 2 minutes
Application.OnTime TimeToCopy, "CopyOver"
End Sub
...copy and paste data
Call Schedule
End Sub
Sub Schedule
TimeToCopy = Now + TimeValue("00:02:00") 'every 2 minutes
Application.OnTime TimeToCopy, "CopyOver"
End Sub
This appears to work fine for a single file. I want to do this for multiple groups of data. So I have used "save as" on the first file to create a number of files with similar format and the same macro names. I have simply changed the DDE formulas to retrieve a different set of data. When running multiple files, I get spurious results. The macros seem to get confused with each other. Some run OK, others don't update at the correct interval (i.e. every 2 mins). Further, it would appear that my live interaction with the worksheet can make the macro work. i.e. when I toggle to the workbook I can see that the macro has not updated for a period of time (say 1 hour), but it then updates as I am looking at the screen.
Should I be using different names for the macros in the different workbooks? Or is there another way to keep multiple macros in different workbooks separate from each other?
