I run a simple macro which opens up 6 data files, the VLOOKUP on my receiving
spreadsheet updates, this is then saved and then the data files close. The
data files are overwritten every 30 mins with new data (always in the same
rows). If for whatever reason a data file has been over written with no data,
i.e. is blank, the formula for the VLOOKUP changes from looking for Sheet1 to
#REF which then needs to be changed back. Does anyone know how this happens
and how I can prevent it?

Many thanks

Generally VLOOKUP() produces #REF! when the 3rd argument is larger than
the width of the 2nd argument table, e.g. in

=VLOOKUP(A1, C:E, 4, 0)

Thus you could examine whether the code deletes any columns prior to
refreshing data. No more ideas unless there is something more specific.

Does this help?
Kostis Vezerides