Spreadsheet does not populate until VBA procedure ends.

[GCWesq]

Spent most of the day on this.
I have a procedure which opens a worksheet, copies data from it, and pastes the data into another worksheet.

This works fine if I open and close the first worksheet. It also works properly if I step through the code using F8.

But it doesn't work properly (see below) if I don't open the worksheet, but just read from it - using code from here:
Excel VBA – Read Data from a Closed Excel File or Workbook without Opening it

THE PROBLEM is that the cells in the target worksheet don't show the pasted values until the VBA procedure ends.
This would be OK, but I want to check the pasted data BEFORE proceeding with the rest of the procedure. To do this checking, I have put in a yes/no MsgBox. If I say NO, the cells don't populate until AFTER I have clicked on NO, which, of course, is too late. I want to be able to see the data before deciding if it's OK, as you can imagine.

I have tried the wait function (Application.Wait - same thing happens after the time has elapsed), I have tried putting other code ahead of the MsgBox, I have tried putting the MsgBox in a separate Sub, and possibly other things, but to no avail.

Any help would be greatly appreciated.

Thanks.

 

[BSALV]

copy it first to an auxiliary worksheet and if later, you agree, copy it to the desired range.

 

[GCWesq]

Thanks, BSALV.
However, I'm trying to avoid opening an extra sheet. Otherwise, I can just open the source document, which does work, as noted.

 

[jasonb75]

Try reactivating screen updating at the point in the procedure where you want to check it.

 

[GCWesq]

With a single stroke of the pen typewriter keyboard, he wipes away eight hours of desperate sweat and tears!
Many thanks, jasonb75. That did it!!!
As Rocky said to Mugsy in a famous Bugs Bunny episode: "I don't know how yuz dunnit, but I know yuz dunnit!"

rocky mugsy bugs at DuckDuckGo

DuckDuckGo. Privacy, Simplified.

duckduckgo.com