That sounds as if it will work, though you will have to fix the values
before closing the 2 workbooks.

A similar approach would be to open each workbook in turn, then move
the sheet into the file containing the macro thereby automatically
closing the rota workbook (unchanged), and then the macro could
continue by establishing the formulae for you. The new sheets could be
renamed This_week and Last_week (or something similar), so the
formulae will always refer to the same (internal) sheet names.

You could also think about downloading the free add-in morefunc - this
has an INDIRECT.EXT function which is meant to allow you to get data
from closed files. I don't have it so I can't comment on it, but I've
seen lots of recommendations in other posts.

Hope this helps.

Pete

On Jul 30, 10:53*pm, robzrob wrote:
Hello Pete

Thought somebody might say that. *Would the answer be at the end of a
road something like this: Set up the worksheet using the INDIRECT
function, then VBA code thus: Open the 2 files I'm interested in (this
week and next week) whenever the particular worksheet in my new
workbook is selected, update, close the 2 files. *What do you think?