Update of another workbook with a vba macro

Hi @Jacques Abel ,

Thanks for reaching out.

The fact that this works in debug mode but not when the macro runs normally does suggest the issue is more likely related to Excel state or timing than to the general VBA approach itself.

Since you already said you are explicitly saving and closing the target workbook, I would not treat the basic Save / Close pattern itself as the real issue here. I would look more closely at what Excel may still be doing in the background at the moment the close happens.

The adjustment I would try is to keep the same workbook-object approach, avoid ActiveWorkbook, and add a small processing gap between the save and the close so Excel can finish any pending events, calculation, or async activity.

wbTarget.Save
DoEvents
Application.CalculateUntilAsyncQueriesDone
wbTarget.Close SaveChanges:=True

If the workbook depends on calculation, you can also force calculation before the save. It is also worth confirming that the target workbook is not opening as read-only, because that can make the macro appear to run correctly while nothing is actually written back to disk.

If events, queries, or other automation are involved, temporarily disabling events during the operation can also help stabilize it:

Application.EnableEvents = False
' your logic here
Application.EnableEvents = True

I would also keep an eye on where the file is stored. If it is on OneDrive, SharePoint, or a network location, sync or background file handling can sometimes interfere with this kind of automation. The same goes for workbooks using Power Query, external links, RTD, or anything asynchronous.

If Save still does not persist the changes, a useful diagnostic test is to try a same-name SaveAs, because that forces Excel to write the file again more aggressively.

Overall, your approach is valid. Based on the behavior you described, this looks more like Excel is not fully completing the save process when the macro runs at full speed than like something fundamentally wrong in your VBA.

If this started right after a specific Excel update and the above does not stabilize it, I would check the exact build under File > Account > About Excel and compare it with Microsoft's official update history for Microsoft 365 Apps:

https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date

You can also use Microsoft's overview of update channels to confirm which release channel the installation is on:

https://learn.microsoft.com/en-us/DeployOffice/overview-of-update-channels-for-office-365-proplus

That gives you a way to confirm whether the behavior started with a specific build and whether a newer build is already available for your update channel.

If you can reproduce it consistently on the same build, it would also be worth sending feedback from Excel through File > Feedback > I don't like something, so the product team gets the exact build details along with the report.

Hope this helps! If my explanation and the information I provided were helpful to you, I would greatly appreciate it if you could follow the instructions here so others with the same problem can benefit as well.

We are under new white house administration ==> math changes
If MSFT also changes logic it will be a big mess.
So, fix it quickly

The described behavior matches recent Excel issues where workbook changes made by VBA do not persist unless execution is interrupted (for example, when stepping through in debug). These issues are typically caused by changes in recent Excel builds and often relate to calculation, events, or macro/ActiveX handling.

From the available information, the practical workarounds and checks are:

1. Ensure macros and ActiveX are fully enabled
   • If the target workbook uses macros or ActiveX controls, verify that macro and ActiveX content is enabled. Similar issues (for example, Run-time error 438 after Office 365 updates) have been resolved by enabling ActiveX/macro content in Trust Center settings.
2. Avoid relying on automatically running procedures behind worksheets/ThisWorkbook
   • Automatically running procedures (such as Auto_Open, Auto_Close) or event procedures stored “behind” a worksheet or ThisWorkbook may not behave correctly after updates.
   • Move any automatically running code that opens/edits/saves the other workbook into a standard VBA module, and call it from there. This is the documented workaround for reliability of auto-run code.
3. Use explicit save and close on the edited workbook
   • After making changes to the secondary workbook, explicitly call:

      wbTarget.Save
      wbTarget.Close SaveChanges:=True
   

   • Avoid relying on implicit save prompts or on the application closing to trigger saving.
4. Check for code that resets the Saved property
   • Code that sets ThisWorkbook.Saved = True or ActiveWorkbook.Saved = True before saving or closing can cause changes not to be written.
   • Ensure no part of the macro sets the Saved property to True on the target workbook before Save is called.
5. Temporarily disable automatic recalculation or volatile behavior
   • There are known cases where large/complex workbooks with heavy calculation or volatile functions interact badly with recent Excel builds, affecting open/save behavior.
   • As a diagnostic step, set calculation to manual before opening and editing the secondary workbook, then save and close it, and only then restore the previous calculation mode.

If the problem started only after a specific Excel update and the same macro and workbooks worked previously, and the above adjustments do not resolve it, the remaining options are:

• Install the latest Excel build (if not already) because similar regression bugs (for example, with pivot tables in version 2509) have been fixed in subsequent builds.
• If the issue persists on the latest build, report it through official Microsoft support channels with a minimal reproducible workbook and macro, as this matches a regression pattern that requires a product fix.

References:

• VBA code "behind" a worksheet or a workbook may not work in Excel
• Use Saved property to determine if a workbook has changed
• Run Time Error 438 in Excel Sheet - Microsoft Q&A
• Version 2509 of Microsoft Excel cannot open a workbook with a simple sum subtotal in pivot table. - Microsoft Q&A
• Excel files now not opening. - Microsoft Q&A