I'm trying to run a macro which, among other things, copies a large range of formulae and then pastes the results as values into the same range. The VBA for pasting the selected range is:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Every time I get to the pasting stage, I get the run-time error "-2147417848 (80010108) Automation error: The Object Invoked has disconnected from its clients." This happens even if I have as few applications running as possible.
I came across the DoEvents function and wonder if I could use it in my VBA code. The examples I've seen online so far don't explain how you pass control to the operating system for a specific task - in this case, pasting data as values.
If DoEvents is appropriate to use here, how would I go about it?
Many thanks.