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
Bookmarks