VBA Macro copy a sheet / it is disappearing

Hi AlphaFrog,

Thank you for the response I appreciate it.

The formula is pasted in when the macros are ran, but the issue is the Lookup Sheet that I have in my file is no longer there.
When I run the report I get a file like this, while the formula for column y is correct, the sheet the VLOOKUP references is no longer there.