VBA workbook_Open problem

[sthyne]

I am looking for vba to prevent workbook_open from executing in new save as file without changing the original file. I have a template that I open with a workbook_Open module to auto generate a sequential number. I want to save the new file to a different location with a new name but I dont want the new file to execute the workbook_Open module. Everything I try ends up changing the template as well.

 

[Logit]

.
It would help a lot to see your code.

 

[sthyne]

.
It would help a lot to see your code.

Private Sub Workbook_Open()
'Step 1: Protect the sheet with a password
Sheets("PG S7 Reaction Tank #210").Unprotect Password:="4848"
Worksheets("PG S7 Reaction Tank #210").Range("l23") = Worksheets("PG S7 Reaction Tank #210").Range("l23") + 1
'Step 2: Protect the sheet with a password
Sheets("PG S7 Reaction Tank #210").Protect Password:="4848"
End Sub

Private Sub CommandButton17_Click()


Dim Path As String
Dim filename1 As String


Path = "N:\BKEP Materials Grand Island\Lab\2018 Production Batch Sheets-Log\Available PMAC Batch Sheets\PG S7"
filename1 = Range("A201").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Path & filename1 & ".xlsm", FileFormat:=52
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

 

[Logit]

.
What is the path (location) where you want the file to be saved ?

What is the extension of the file now (.xlsx ; .xlsm ; .xlsb ??? )

 

[Logit]

.
Try this ... paste into a Routine Module with CommandButton on the worksheet to activate it.

Option Explicit


Sub SaveBookAs()


Dim Path As String
Dim filename1 As String


Path = "C:\Users\My\Desktop\"   '<-- edit path as required
filename1 = Range("A1").Text


Application.DisplayAlerts = False


    ActiveWorkbook.SaveAs Filename:=Path & filename1 & ".xlsm"
    
Application.DisplayAlerts = True


Application.Quit
Application.Visible = False


End Sub

You will need to delete the Workbook_Open macro.

 

[sthyne]

Worked Great, Thank You!

 

[Logit]

You are welcome.