Executing macros in multiple workbooks with the same name

Juroko

New Member
Joined
Feb 27, 2017
Messages
1
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:
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

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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Watch MrExcel Video

Forum statistics

Threads
1,130,201
Messages
5,640,818
Members
417,168
Latest member
StumpoC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top