RTD not updating when second file is opened in VBA


Active member
I have a few workbooks that has the "MARK" RTD value for each brokerage in on cell and I want to save this value as a static number in another workbook that consolidates all the brokerages in one workbook
I have a few separate worksheets in this workbook for each brokerage
So the macro code opens a workbook from one brokerage and copies a range of cells that includes this RTD value and saves it to the consolidated workbook into a worksheet for the appropriate brokerage
If I run the code for each brokerage separately it all works fine and I am getting the right value

Of course I would like to run a code that includes the code for each brokerage so I can run the code once instead of each one separately
So I put all the separate individual macros into one large macro but I am having a problem after the second worksheet where the value for the MARK shows up as 0

After running the code step by step I saw that when the second workbook is opened the value is already 0
Than after making a separate short macro that just opens each workbook without doing any copying the MARK value is correct

I was just wondering if anyone ran across this problem and what the solution might be
Why doesn't the RTD update when the second file is opened while the macro is running ?

I guess one workaround might be to open all the workbooks with one macro and than run a separate macro that copies all the data from the different workbooks