Importing data through macro requires macro to be run twice.

[johnstcb]

Hello,

I have an excel file on Sharepoint that I use a macro in to import data from other Sharepoint Excel files (these are calculated values in the input excel file rather than hard-coded values), and then this data feeds into pivot tables in the output file. What I find is if I run the macro once I get "#ref" for the imported data on the output file and it seems to have copied it but not calculated it. When I run it a second time it works. If I run the macro once but with VBA open, it also works.

Note this is similar issue to a couple of other threads I've seen but I can't get the maco to work using excel import data (and therefore can't remove the tick from the enable background refresh). I think this is due to the use of Sharepoint and the file paths/names being specified in the file rather than the macro.


Do you know how to make it work without a) running the macro twice and b) using the excel import data function?

In addition, a possibly related issue is that if I change my input data, my output file does not pick up the new data when I re-run the macro. It only picks it up when I close the output file and reopen it.

Macro is as follows (please excuse any poor coding... I'm self taught):

Please Login or Register  to view this content.

[johnstcb]

Can anyone help with my query please? Do you need any more information? Thanks.