Not able to execute macro for Backup

[Santoshmoni]

Hi,
I am trying to write macro for backup of excel sheet, but while executing the macro its giving the error.

"Sub Macro1()
'
' Macro1 Macro
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As String
Dim ans As Integer
Dim fname As String
Dim MyDate
Dim MyMonth
MyDate = Date
MyMonth = Month(MyDate)
Msg = "Would you like to back up file?"
ans = MsgBox(Msg, vbYesNo)
If ans = vbYes Then
ThisWorkbook.SaveAs Filename:="D:\Backup\" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date) & "-" & ThisWorkbook.Name
End If

'
End Sub"

[JBeaucaire]

1) Hi Santoshmoni... Welcome to the forum.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to EDIT that post above and put code tags around that code you used. (Like shown in my signature)

2) I can offer some more specific suggestions more efficient/reliable code after you fix the post above, or you can search on your own about using the .SaveCopyAs method.