VBA Copy workbook as .xlsm but delete macro

[Kimchi]

Hello,

I need to write a code that's supposed to automatically refresh a Query, then save the current workbook and close it when opening the file.
The problem is, the file also needs to be sent out. So I want to make a copy of the file that doesn't run the private sub. However, it does have other macro's it can run so I found out that saving it as .xlsx doesn't work. However, when saved as .xlsm the private sub automatically runs. Any idea's how to bypass this? Current code:

Sub Test() 
Dim x As String, y As String 
x = ActiveWorkbook.Path
y = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) 

Application.DisplayAlerts = False 
Application.ScreenUpdating = False 

Workbooks("Quantity en revenue overview all partners Hardcopy v3 - Copy (2).xlsm").Activate 
ActiveWorkbook.Connections("Query - Details").Refresh 
ActiveWorkbook.SaveAs x & "" & y & "_" & Format(Date, "ddmmyyyy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Workbooks("Quantity en revenue overview all partners Hardcopy v3 - Copy (2).xlsm").Close SaveChanges:=True 
Application.ScreenUpdating = True 
End Sub

If anything needs clarification please let me know.

 

[Kimchi]

Note: The Query is in workbook A and workbook A will be automatically opened every morning. The idea is that it will refresh the query in workbook A, then save a copy of workbook A without the private sub (the sub is currently not private yet since I'm testing it out).

 

[Kimchi]

Note2: I also get the error Subscript out of range at the end, however it does seem that the macro fully does it's job before giving the error.