I have created a macro to copy and paste the values for the data. However, even if I create a "Application.Wait" line in the macro, the cell calculating the"XPathOnURL" is copied and pasted as "#GETTINGDATA"
No matter how long I increase the time for the "Application.Wait" code, "#GETTINGDATA" is still what is copied and pasted.
I can only put this down to, excel does not process the "XPathOnURL" formula while a macro is running. As soon as the macro is finished, all of the data appears. The problem with this, is that with thousands of lines of data, excel becomes almost too slow to run a second separate macro to copy and paste the values for the data.
My solution here, is to create two separate macros, one for creating, say 50 lines of the "XPathOnURL" formula, and a second macro to copy and paste the values.
However this would still be manually operated, and quite tedious for thousands of lines of data.
Any assistance to this problem, would be greatly appreciated.