VBA Macro copy a sheet / it is disappearing

**FlatFish** · 10-06-2021, 03:23 PM

Hi everyone,

I am new to VBA and macros and I recently started a new position and am updating the macros for this document. What happens is I am sent a report and I rename the file to today's date in a folder. There is a VBScript that opens an excel file template that has a Template sheet and a lookup sheet.

The VBScript opens the template and executes the two macros. One of the macros sends an email which is working fine, but the other one copies all the information needed from the report that is renamed to today's date.

Upon completion of the script the error is in column Y of my report because it is a VLOOKUP function that references a lookup sheet that is on the original template.

How do I make it so that sheet remains. I have attached what I believe is needed.

The excel help file has the macro I believe is causing the issue as the other macro sends an email. It also contains the Lookup sheet that gets deleted or not copied.

Any help would be greatly appreciated!

**AlphaFrog** · 10-06-2021, 10:56 PM

Your example file doesn't show the VLOOKUP formula. I'm assuming the VLOOKUP formula in column Y is already in the original daily report before you copy it to the template?

If yes, the easiest fix would be to convert the Template VLOOKUP formulas to values after copying column Y to the template, but before closing the original report.

Please Login or Register  to view this content.

**FlatFish** · 10-07-2021, 08:46 AM

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.