Excel crashes on ".save as" in macro

[cknnugget]

The macro was working before but now it crashes on step "'3. Save new workbook in specified file and close copy".
The address was confirmed in immediate window and looks correct
?Worksheets(Sheet9.Name).Range("C4")&Range("E51")
D:\Test items\Memos\1 Pending\2022-10-16-Name--Memo-20.xlxs
However, it saves the file as File for type and then crashes excel
"D:\Test items\Memos\1 Pending\6D9D5C40"
Here is the Macro any Ideas why it would continue to crash??
Dim path As String
path = Worksheets(Sheet9.Name).Range("C4")
Dim memono As Long
memono = Range("A1")
Dim fname As String
fname = Range("E39")
Dim Answer As VbMsgBoxResult
Answer = MsgBox("Memo cannot be changed. Review all and copy to ICHRT before submitting. Click Yes to Save & no to continue working", vbYesNo, "REVIEW MEMO & SAVE")
If Answer = vbYes Then
Application.DisplayAlerts = False
'1. Create copy of Memo in a new Workbook
Worksheets(Sheet4.Name).Visible = True
Worksheets(Array(Sheet1.Name, Sheet4.Name)).Copy
Worksheets(Sheet4.Name).Visible = False
'2. Remove Buttons from copy

'3. Save new workbook in specified file and close copy
With ActiveWorkbook
.SaveAs Filename:=path & fname, FileFormat:=51
.Close
End With
'4. Hide Sheet
Worksheets(Sheet4.Name).Visible = False
'5. Clear Template
Range("C8:L8").ClearContents
Range("C10:L10").ClearContents
Range("C12:L12").ClearContents
Range("C14:L14").ClearContents
Range("C16:L16").ClearContents
Range("C18:L18").ClearContents
Range("C20:H20").ClearContents
'6. update Memo number on template
Sheets(Sheet1.Name).Unprotect Password:=""
Range("A1") = memono + 1
Range("I20:J20").ClearContents
Sheets(Sheet1.Name).Protect Password:=""
'7. Save Template
ThisWorkbook.Save
Application.DisplayAlerts = True
'8.Message Process complete
MsgBox "The Memo has been saved and is pending review. You can now close the Memo Template"
Else
Exit Sub
End If




End Sub

 

[ouvay]

excels crash ever so often.. why not try and see if your autorecovered file behaves? if it does, just save as there

 

[StephenCrump]

The address was confirmed in immediate window and looks correct
?Worksheets(Sheet9.Name).Range("C4")&Range("E51")
D:\Test items\Memos\1 Pending\2022-10-16-Name--Memo-20.xlxs

fname = Range("E39")

Where's your filename? E39 or E51? What's in E39? Just "6D9D5C40"?

What exactly do you mean by "crashes Excel"? What happens?

Have you shown all your code, e.g. have you omitted any code here:

'2. Remove Buttons from copy
'?????
'3. Save new workbook in specified file and close copy

that might be making ThisWorkbook the ActiveWorkbook, which you close?

 

[ouvay]

Where's your filename? E39 or E51? What's in E39? Just "6D9D5C40"?

What exactly do you mean by "crashes Excel"? What happens?

Have you shown all your code, e.g. have you omitted any code here:

'2. Remove Buttons from copy
'?????
'3. Save new workbook in specified file and close copy

that might be making ThisWorkbook the ActiveWorkbook, which you close?

Ah! I see it..

This line is questionable

With ActiveWorkbook
.SaveAs Filename:=path & fname, FileFormat:=51
.Close
End With

better assign new workbook to a different var and then close that..

 

[cknnugget]

Where's your filename? E39 or E51? What's in E39? Just "6D9D5C40"?

What exactly do you mean by "crashes Excel"? What happens?

Have you shown all your code, e.g. have you omitted any code here:

'2. Remove Buttons from copy
'?????
'3. Save new workbook in specified file and close copy

that might be making ThisWorkbook the ActiveWorkbook, which you close?

E51 is the file name. I do not know where "6D9D5C40" comes from instead of naming the file random text is generated for the file name

 

[cknnugget]

E51 is the file name. I do not know where "6D9D5C40" comes from instead of naming the file random text is generated for the file name

When when the excel copy is generated, the workbook partially shows on the screen. there is about a 5 second pause then excel shuts down.

 

[HaHoBe]

Hi,

should the extension not be xlsx?

?Worksheets(Sheet9.Name).Range("C4")&Range("E51")
D:\Test items\Memos\1 Pending\2022-10-16-Name--Memo-20.xlxs

Ciao,
Holger

 

[cknnugget]

Hi,

should the extension not be xlsx?



Ciao,
Holger

The goal was to remove the Macros. To rule it out I tried to change the extension to XLSM and 52 but the same issue occured.

 

[StephenCrump]

E51 is the file name.

Yet your code is using:

fname = Range("E39")

 

[StephenCrump]

You haven't answered this question:

Have you shown all your code .... ?

Does it make any difference if you do this?

Dim wbCopy As Workbook

'....

Worksheets(Array(Sheet1.Name, Sheet4.Name)).Copy
Set wbCopy = ActiveWorkbook

'....

With wbCopy
    .SaveAs Filename:=Path & fname, FileFormat:=51
    .Close
End With