Insert copy of sheet into another workbook

[jharvey87]

Hello everyone,

I'm trying to use a macro to make a copy of a worksheet within the workbook i'm working in and place a copy in the final workbook to be distributed. I was trying to do this by macro where I don't have to open the final workbook, just run the macro and it creates a new tab within that workbook. I tried using some code such as below but I'm not having any luck.

Sub CopySheetToClosedWB()
Application.ScreenUpdating = False
 
    Set closedBook = Workbooks.Open("U:\Shared drives\Branch Reports\PM Scorecard\18 - PM Scorecard.xlsx")
    Sheets("SCORECARD").Copy Before:=closedBook.Sheets(1)
    closedBook.Close SaveChanges:=True
 
Application.ScreenUpdating = True
End Sub

In my working excel file, I want to copy the tab titled "Scorecard" and place it in the excel workbook titled 18-PM Scorecard. The only other option I would love to do is have it rename the sheet in the final workbook. Such as change it to "SCORECARD -todays date". Is that possible?




Thanks for the help.