Understanding cause of a VBA error in Excel 2007.

Question

Hi,

I am trying to save the current sheet to a separate file on exiting Excel 2007. The code I am using is :

Sub Save_Current_Worksheet_as_New_File()

 'Gets the name of the currently visible worksheet

    Dim Filename As String

    Filename = ActiveSheet.Name

'Puts the worksheet into its own workbook

    ThisWorkbook.ActiveSheet.Copy

'Saves the workbook - uses the name of the worksheet as the name of the new workbook

    ActiveWorkbook.SaveAs "C:\2024.xlsx"

'Closes the newly created workbook so you are still looking at the original workbook

    ActiveWorkbook.Close

End Sub

I activate this using the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Call Save_Current_Worksheet_as_New_File

End Subline

On exiting the spreadsheet, I get error 1004 as in the attached screenshot

followed by the line in error when I select 'Debug' as shown in image two.

I don't understand what is going wrong.

Also at this stage I have the original spreadsheet as well as 'Book5' which appears to be a copy of the original.

Any suggestions gratefully received.

Regards, Graham.

Answer 1

The root folder of a drive may be off-limits to you. Try saving the file to the same folder as the workbook with the code:

ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & Filename & ".xlsx", xlOpenXMLWorkbook

Answer 2

Thank Bernie,

That works a treat.

Many thanks, Graham.