Insert a macro in an about-to-be-created workbook

[kweaver]

I have a macro that spins off a specific sheet from a workbook and saves it in a given path.
Before saving it, I'd like to have a macro placed in this about-to-be-saved workbook.
I have the relatively short code for the macro to be place in it, just don't know what I should do to go about this association with the new workbook.

 

[Legacy 456155]

Here is an example that uses two workbooks. source.xlsm and target.xlsm.
The following code assumes that they are both in the same path. All code goes in the workbook classes.
See 'kweaver' folder that contains two example file here. The example uses a shape with an assigned/re-assigned macro in addition to OnKey.


In source.xlsm workbook class module. The one that does the formatting and spawns worksheets.
Perform your formatting before calling.

Sub FormatAndCopyToTargetWorkbook()
    With Workbooks.Open(Path & "\target.xlsm")
        ActiveSheet.Copy After:=.Sheets(1)
        Application.DisplayAlerts = False
        .Sheets(1).Delete 'delete the blank sheet in target
        Application.DisplayAlerts = True
        .Sheets(1).Name = Format(Date, "YY.MM.DD") 'you mentioned a date stamp?
        .SaveCopyAs Application.GetSaveAsFilename(, "Macro Enabled Workbook (*.xlsm), *.xlsm", , "Save Destination Workbook")
        .Close False
    End With
End Sub

In target.xlsm workbook class module.
This is the workbook that will receive the copied worksheet.

Private Sub Workbook_Activate()
    Application.OnKey "+^{C}", Me.CodeName & ".InsertCopyRow"
End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "+^{C}", vbNullString
End Sub

Public Sub InsertCopyRow()
    MsgBox "InsertCopyRow code runs here"
End Sub

 

[kweaver]

Thank you very much. I'm having some difficulty adapting what you suggested.
What I need to do is copy the sheet called "TimeSheet" to the new xlsm file which I've created and saved as "ReformattedFile.xlsm".

What adjustment(s) do I need to make?

 

[kweaver]

Dataluver: THANK YOU!!! for all of your help. After a good sleep I worked through your code and have been successful implementing what I needed.
Further attests to the fantastic help in this forum.

Cheers!

Kevin