Hi guys, I have a report template that we update daily with raw data, and we email another copy of this report but only with a few tabs copied as values. There's a couple steps to it: Copy 5 tabs to a new workbook in the same folder, rename the file to include today's date, rename three tabs to include the current Month name like "Dec 2021 Summary", and closing all Excel instances so nothing is corrupted.

The code works but it takes 4-5 minutes to run...and I don't know if it's because the code needs a lot of memory or it's just inefficient. Was wondering if someone can look at it. Thanks!

A second note that's more of a question. This macro is stored in the report template so I have to open it to run. I've read that I can instead use a VBScript whether the file is open or not. How would I add another line of code and convert this to a script?

Sub CopyFvO()
  Dim wb As Workbook
    Dim SheetNames As Variant, Key As Variant
    Dim FName As String, FPath As String

    Application.ScreenUpdating = False


    SheetNames = Array("Current Month", "Next Month", "Next 2 Month", "Forecast vs. Orders", "KP - Machine")
    'store the sheet names you want to copy
    
    Set wb = Workbooks.Add 'set a workbook variable which will create a new workbook

    'loop through the sheets you previously stored to copy them
    For Each Key In SheetNames
        ThisWorkbook.Sheets(Key).Copy After:=wb.Sheets(wb.Sheets.Count)
    Next Key

    'delete the first sheet on the new created workbook
    Application.DisplayAlerts = False
    wb.Sheets(1).Delete

    FPath = "\\alteryxfileshareprod\Alteryx\UnRestrictedAccess\Shared Services\Forecast vs Orders"
    FName = "Forecast vs. Orders " & Format(Date, "mm.dd") & ".xlsm"
    If Dir(FPath & "\" & FName) <> "" Then Kill FPath & "\" & FName
    wb.SaveAs Filename:=FPath & "\" & FName, FileFormat:=52

    For Each Key In SheetNames
        wb.Sheets(Key).Select
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
         Application.CutCopyMode = False
    Next Key
    
    wb.Sheets("Current Month").Name = Format(Date, "mmm") & "-" & Year(Date) & " Summary"
    wb.Sheets("Next Month").Name = Format(DateAdd("m", 1, Date), "mmm") & "-" & Year(DateAdd("m", 1, Date)) & " Summary"
    wb.Sheets("Next 2 Month").Name = Format(DateAdd("m", 2, Date), "mmm") & "-" & Year(DateAdd("m", 2, Date)) & " Summary"
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With