Hi,

I managed to do what you said, and in the macro while it copied the formula into the range of cells I set Application.DisplayAlerts to False, then when it finished set it back to True.

This prevented any window popping up to ask where the worksheet was located.

Then I right-clicked on the spreadsheet and said "Move or Copy..." and selected "Make a Copy". I told it to copy the spreadsheet into another workbook I had open. This is the workbook that contains the spreadsheet which I want the VLOOKUP to work on.

That went successfully. However, the VLOOKUP formula did not produce any results. All the cells stayed blank. I had to manually edit a cell and press the ENTER key for it to actually calculate the formula.

Calculations are set to automatic in the options, and even clicking "Calc Now" or "Calc Sheet" did not refresh them.

Does anyone have any suggestions as to how to get around this? This is a program other people need to use, so I can't just tell everyone they're going to have to manually input formulas every week (that wouldn't go down well).