vba - Using code to delete code

Question

I have googled this and found multiple solutions for this but none of them have worked for me. I am inclined to believe this is because of my limited knowledge and ability when it comes to programming in VBA in Excel. If this is a repeat question of questions already posted here I apologize.

The question:

I have a code that executes on open of Excel Workbook. The code that executes saves the workbook as a new workbook under the name of a value located in a cell. I need the code that executes on open of Excel Workbook not to run on the new copy that has been created. The best way that I can think of to go about this would be to have a code that deletes part of or all of the code that executes on open of Excel Workbook. I have seen solutions for this before, I know that a solution exists but I do not know how to properly implement the code into my program so it works, I don't even know if the code I found will work which is why I have not provided it here. Here is the code that I have so far with comments explaining what I need in the code. Any help with this would be huge.

Thanks

Private Sub Workbook_Open()

 'Saves filename as value of C10 

Dim newFile As String, fName As String

fName = Range("C10").Value

newFile = fName & " " & Range("E9").Value

ChDir Range("A8")
ActiveWorkbook.SaveCopyAs Filename:=newFile

'Need code to delete a line of code or all code to prevent the code above from executing when the new Worksheet that has been created is opened.

ActiveWorkbook.Close False
End Sub

Answer 1

Well, this code will delete the event. You save a copy of your workbook, so my idea is

1. you do whatever you do
2. Delete the Workbook_open Macro
3. Save to a new workbook (that part of macro won't be)
4. DO NOT SAVE CHANGES IN YOUR ORIGINAL WORKBOOK, so the Workbook_Open macro will be always secure.

Anyways, test it first several times to make sure it works. Altering VBA code with VBA itself can produce weird things somethimes.

Hope this helps.

All credits go to OZGRIZ

Also, make sure you check in Excel Options->Trusted Sites->Settings of Trusted Sites-> Macros Setting-> Check Trust access to Visual Basic Editor

Sub DeleteWorkbookEventCode()

''Needs Reference Set To _

    '"Microsoft Visual Basic For Applications Extensibility"

'Tools>References.

'Also, make sure you check in Excel Options->Trusted Sites->Settings of Trusted Sites-> Macros Setting-> Check Trust access to Visual Basic Editor

Dim i As Integer
Dim MyStart, MyEnd As Integer

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

MyStart = 0

    For i = 1 To .CountOfLines Step 1
        If .Lines(i, 1) = "Private Sub Workbook_Open()" Then MyStart = i

        If .Lines(i, 1) = "End Sub" And MyStart > 0 Then
            MyEnd = i
            Exit For
        End If
    Next i

    .DeleteLines MyStart, (MyEnd - MyStart) + 1

End With

End Sub