I have this macro that is causing me nothing but headaches. I have been at it for 24 hours now. I have a workbook to perform maintenance called "Quote Log Maintenance Macros". It has to loop through many workbooks (60ish) and replace a module called utilities. What I get is that sometimes it works and other times it wont. It seems there is a problem if I save and close the workbook using vba code. That means if I set my constant LIVE to "False" it usually works because the macro ends and I do the save and close. If I launch my testing launch point "Sub TestUpdate94" it usually works. However if I try to run the main function "Function Update94" it through the loop (as it should) it runs 20% of the time or less.
I have added the Microsoft Visual Basic for Applications Extensibility 5.1
I have tried all kinds of weird things to make it work. I tried to change the name of the module before I delete it. That seemed to help so it doesn't bring in "Utilities1" instead of "Utilities". Looped through to delete the module 100 times. That made little difference. Added extra calculates. Made sure events are enabled. Activated the workbook over and over again to make sure it is the active one.
I am at a loss. It shouldn't be that hard. Delete Utilities Module. Import new utilities module. Done. What is it that I am missing? Some events don't seem to be firing right.
Do I need to use a personal macro workbook or something? Is there a secret to using vba to make changes to a macro module?
Thank you!
Bookmarks