How to Prevent Background Data Model Refreshes in Excel VBA

Open the VBA Editor (ALT + F11), locate your macro, and insert `Application.Calculation = xlCalculationManual` at the beginning of the script to prevent immediate formula evaluation.

Copy your formulas or worksheets as intended within the macro, then execute a refresh on the specific Data Model connection using `ActiveWorkbook.Connections("YourConnectionName").Refresh`.

Write a `Do While` loop that checks `ActiveWorkbook.Connections("YourConnectionName").OLEDBConnection.Refreshing`. Inside the loop, use `Application.Wait` to pause macro execution momentarily until the property returns False.

Once the loop completes (indicating the refresh is finished), insert `Application.Calculation = xlCalculationAutomatic` and `Application.Calculate` to safely evaluate the CubeValue formulas.

In the VBA Editor, insert a new module and define a subroutine named `DisableBackgroundRefresh`.

Write a `For Each conn In ActiveWorkbook.Connections` loop to iterate over every active connection in the file.

Inside the loop, check if the connection is an OLEDB type. If it is, assign `conn.OLEDBConnection.BackgroundQuery = False`.

Call this subroutine at the very start of your primary worksheet-copying macro to ensure all subsequent data retrievals wait for completion before moving to the next line of code.