I've starting using files, where I set a pivot table looking at another
closed workbook. I use the Workbook_Open event to run a macro that runs
through the pivot tables (between 2 and 5 currently) and updates the
contents, so when the file is opened, it gives up to date figures.

When I email these sheets the macro fails, because it can't update the pivot
tables. Having a quick look, and for the area used by the pivot table, the
filename has changed. Rather than look at the S drive (the mainframe where
just about all information is saved) these tables are now looking at the C
drive, therefore the file doesn't exist (not on the C drive) and cannot
update the pivot table.

Anyone know why this happens? and what I can do to stop it. Sending sheets
to people so so much easier than saving them on the mainframe and giving
people the address.