VLOOKUP to different folder on share drive

I have a functioning macro in a template that performs a VLOOKUP into a separate file with-in the same folder with the source file being open.

However, if the template is saved to another folder first, the VLOOKUP doesn't work.

Is there a way for the macro VLOOKUP to function from the new location of the saved copy of the template (different folder on the same share drive)?

Thanks

Are you running a macro for the VLOOKUP or is the lookup function typed into a cell as a formula? If it's a formula, you should be able to update the directory location in the formula i.e "e:\docs&settings\shared\excel\tempfile.xlsx".

If this is a macro, you should also be able to edit the destination to reflect the new location of the file within the macro code.

The shared folder should not have an impact, however if this is a file that might be used by more than one person, you might look into the sharing options that are available within Excel to assist with functionality of the workbook.

The VLOOKUP is with-in the macro

The template is used by multiple users and is opened then save-as different file name in different folders each day. If the macro is executed prior to saving file with new name/location all is fine.

If save-as new name/location is done first then macro VLOOKUP doesn't work; unless the source file is open.

Just trying to make it work either way without having to open source file

If you are using a macro, what about having the macro reference the new file name and then append the lookup function to reflect the updated file name?

I'd provide an example, but my VBA skills are still a bit novice.