VBA Code to Save Copy File without Macro Button and VBA Code on Copy?

Hi All,
Just wanted to know is it possible to run VBA save new copy of file without macro button and VBA Code?
My master copy is .xlsm format. After clicking the macro button on my master copy, it will become .xlsx file.

Can we or does VBA able to run a code without macro button and VBA Code on the new saved copy? Except the formula on all the cells remain unchanged.
Hope to get advise and guide from you guy

My current code as below, result from google and modify a little.

Sub SaveScreeningWithNewName()
    Dim NewFN As Variant
    ' Copy Screening to a new workbook
    Sheets(Array("LOCAL", "EXWORK", "HBL")).Copy
    NewFN = "\\CYCHUA-PC\Users\Public\Documents\Bill\" & Sheets(1).Range("c2").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextScreening
End Sub

You don't need a button for that u can try using beforeclose macro.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Only one thing is if you save a copy as .xlsx then when you open the copy the macros won't work or I think you will get an error that will say that your sheet contains macros and so on...

Look like will be more troublesome on that way, no any other way? or just clear out the VBA code on new save file copy?

You wan't the copy saved with no macros inside the copy? just an ordinary workbook .xlsx?

Originally Posted by stojko89

You wan't the copy saved with no macros inside the copy? just an ordinary workbook .xlsx?

Yeah, want the newly auto saved copy to be normal .xlsx excel file with no vba code or/and macro.

I will need to change the "button1" to the exact name where i placed in the button box only? Rest of the code, remain unchanged?

ActiveSheet.Shapes.Range(Array("Button 1"))

And don't forget about .Delete at the end

I dont know what is my button name. How to know or see?

Click on the button and above Cell A1 the name will show in a small window or right click it

I placed it exactly the same as below,

Sub SaveScreeningWithNewName()
    Dim NewFN As Variant
    ' Copy Screening to a new workbook
    Sheets(Array("LOCAL", "EXWORK", "HBL")).Copy
    ActiveSheet.Shapes.Range (Array("o9"))
    NewFN = "\\CYCHUA-PC\Users\Public\Documents\Bill\" & Sheets(1).Range("c2").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextScreening
End Sub

Showing run time error 438

try this:

Sub SaveScreeningWithNewName()
    Dim NewFN As Variant
    ' Copy Screening to a new workbook
    Sheets(Array("LOCAL", "EXWORK", "HBL")).Copy
    ActiveSheet.Shapes.Range(Array("o9")).Delete
    NewFN = "\\CYCHUA-PC\Users\Public\Documents\Bill\" & Sheets(1).Range("c2").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextScreening
End Sub

Your button is named o9 ?
I told you not to forget .Delete

Error too, 1004.

This works fine in your file for me:

Sub SaveScreeningWithNewName()
    Dim NewFN As Variant
    ' Copy Screening to a new workbook
    Sheets(Array("LOCAL", "EXWORK", "HBL")).Copy
    ActiveSheet.Shapes.Range(Array("Button 1")).Delete
    NewFN = "\\ANDROMEDA\RedirectedFolders\denis\Desktop\Metalna IMPRO\Excel Test Stvari\" & Sheets(1).Range("c2").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextScreening

Just change the NewFN back to your original path.

Got it now. thanks a lot man!