VBA apply code to ThisWorkbook when sheet copied to new workbook

[hajiali]

Sub chkDatesCopyDutyRoster()
Dim ws As Worksheet, Sh As Worksheet
Set ws = ActiveSheet
Set Sh = Sheets("BUILD")
If Sheets("Results").Range("X1") = True Then
    If DateSerial(ws.Range("H1").Value, ws.Range("E1").Value, ws.Range("F1").Value) < Sh.Range("B1").Value Or _
        DateSerial(ws.Range("H1").Value, ws.Range("E1").Value, ws.Range("F1").Value) > Sh.Range("I1").Value Then
        MsgBox "CHANGE THE DATE OF THE LAST DAY OF BID IN SHEET BID RESULTS TO CONTUINE EXPORT"
        Exit Sub
    End If
ElseIf Sheets("Results").Range("X1") = False Then
    If DateSerial(ws.Range("H1").Value, ws.Range("E1").Value, ws.Range("F1").Value) < Sh.Range("AT1").Value Or _
        DateSerial(ws.Range("H1").Value, ws.Range("E1").Value, ws.Range("F1").Value) > Sh.Range("BA1").Value Then
        MsgBox "CHANGE THE DATE OF THE LAST DAY OF BID IN SHEET BID RESULTS TO CONTUINE EXPORT"
        Exit Sub
    End If
End If
Dim wb As Workbook
Set ws = ActiveSheet
ws.Unprotect Password:="262"
ws.Copy
ws.Protect Password:="262"
With ActiveWorkbook
ws.Cells.Copy .Sheets(1).Range("A1")
    ActiveWorkbook.SaveAs fileName:= _
        "c:\" & Range("G1") & "\" & Range("F1") & ".xlsm ", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End With
Call macro1
Call macro2
Call macro3
End Sub

Using the above code I'm able to copy the Active Sheet to its own workbook and save it to directory. I would like to add the function to copy the following code to the new workbooks "ThisWorkbook" so that when the new workbook opens anytime afterwards it runs test1 and test2

Private Sub Workbook_Open()
    With Sheet1
        Call .test1
        Call .test2
    End With
End Sub

The 2 macros are Public Sub test1() and Public Sub test2() on the sheet that is being copied

 

[Ron de Bruin]

This will give you a start : Import and Export VBA code

 

[hajiali]

Not sure if it will require all that code. Maybe Im did not describe it clear but the code;

Private Sub Workbook_Open()
    With Sheet1
        Call .test1
        Call .test2
    End With
End Sub

would not be in the active workbook under thisworkbook. I would have the above code as a module in the active workbook or saved on file directory as thisworkbook.cls and would like that code to be copied over to the new workbook under thisworkbook and not as a module. not sure if that clarifies or if it will require me to use the codes on the link

 

[mikerickson]

Fully qualify the references, using this workbook's name rather than the ThisWorkbook.

If Workbooks("Workbook1.xlsm").Sheets("Results").Range("X1") = True Then

 

[hajiali]

mikerickson when Im referring to thisworkbook im referring to:

the public sub() above would be in workbooks("TOOL.xlsm") as a macro however I needing another macro in workbooks("TOOL.xlsm") that I can call at the end

Sub chkDatesCopyDutyRoster()

macro so that it will copy the public sub() code to the new workbook created under "thisworkbook" section in the vbaproject.

the

Sub chkDatesCopyDutyRoster()

works fine

 

[mikerickson]

If you want code to be copied when you copy a worksheet to another workbook, you'll have to put that code in the sheet's code module.
Perhaps this work-around would work for you

' in Sheet1 code module

Dim Flag As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Flag Then
        Call test1
        Call test2
        Flag = True
    End If
End Sub

 

[AC PORTA VIA]

not sure if you are looking for this but see below

from Programming In The VBA Editor (cpearson.com)

Sub CreateEventProcedure()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
       

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("ThisWorkbook")
        Set CodeMod = VBComp.CodeModule
       
        With CodeMod
            LineNum = .CreateEventProc("Open", "Workbook")
            LineNum = LineNum + 1
            .InsertLines LineNum, "  With Sheet1"
            LineNum = LineNum + 1
            .InsertLines LineNum, "    Call .test1"
            LineNum = LineNum + 1
            .InsertLines LineNum, "    Call .test2"
            LineNum = LineNum + 1
            .InsertLines LineNum, "  End With"
        End With
    End Sub

 

[hajiali]

Hello AC PORTA VIA I think what you have might be what I need. I am getting User-Defined type not defined on

Dim VBProj As VBIDE.VBProject

whenever the code reaches it.

 

[MARK858]

You need to set a reference to Microsoft Visual Basic For Applications Extensibility 5.3 as it tells you in the link.

 

[hajiali]

You need to set a reference to Microsoft Visual Basic For Applications Extensibility 5.3 as it tells you in the link.

Thanks Mark858 I totally miss the link got to excited about using the code. lol