External file relocated - can't get data

[mtbe]

I have a spreadsheet that reads data from another xls file on another drive. Let's say there are 50 cells in my file that are reading 10 cells each from 5 external files.

At the end of the year, the external file(s) is moved/removed (or archived - zipped) and replaced with a new one with different file names for the new year.

I would like to keep the value of the data in my spreadsheet, even after the external files have been moved/removed. I don't want the cell formulae to keep looking for the external files. Once it gets the external data the first time, I just want the value in the cell.

This would also shorten the time it takes to open the file.

Any ideas?

[JBeaucaire]

That's not a formula, that's a pretty hefty VBA routine. Post your question in The Programming Forum and you'll need to expand your explanation to 100% of the details.

Anyone attempting to help with this would need to know everything you're doing.
1) The existing sheetnames and cell references
2) The existing formulas in your local wb you're using to get the remote data
3) The current filepath structure
4) What you want to happen when the file goes missing, exactly what you want the macro to do in terms of "looking"
5) How the macro is activated to know it needs to go get new info from new locations/filenames

[shg]

Or just copy the cells and paste as values over the original formulas.

[JBeaucaire]

Or just copy the cells and paste as values over the original formulas.

Of course, I completely misread the end of the question. (*shame*)

[shg]

Maybe, maybe not ...

[integra]

At the end of the year, the external file(s) is moved/removed (or archived - zipped) and replaced with a new one with different file names for the new year.

IF IT ONLY FOR "ONCE A YEAR", THEN YOU CAN DO "BREAK LINKS" AND SAVE FILE AS "XXX".