Macro to delete a worksheet before Save As

[Keith24249]

Hi,
I would like to write a piece of code that would run when I click "Save As". I need it to delete a worksheet called "DATA" when I click Save As. I have searched many forums and googled it but nothing seems to work. The workbook is compiled in Excel 2007. Can anyone help. If you would like a copy of the workbook please let me know.
Regards
Keith

 

[jmthompson]

Not sure if it is possible to specify SaveAS, is Save okay? That will catch both Save and Save As:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
Worksheets("DATA").Delete
Application.DisplayAlerts = True
End Sub

Note- there is no error handling, so if there is no sheet named "DATA", then the macro will error out

 

[AlphaFrog]

This will catch only Save As

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
        Application.DisplayAlerts = False
        On Error Resume Next
        Sheets("Data").Delete
        Application.DisplayAlerts = True
    End If
End Sub

Put this code in the ThisWorkbook module.

 

[Keith24249]

Hi AlphaFrog,

Many thanks for your reply and support. This code works great except that when I want to open the saved as copy I get an error message which says
Runtime Error 9
Subscript Out Of Range
Is there anything that you can recommend?
Would a possibility be to delete the code instead of the whole page titled DATA?
Hope you can helpRegards
Keith

 

[AlphaFrog]

When you open the saved as copy and get the error, click on the Debug button. What line of code is highlighted?

 

[Keith24249]

Hi,

The line of code highlighted is as follows:

Worksheets("QUOTE").Range("H2") = Worksheets("DATA").Cells(2, "E").Value

M
Thanks
Keith

 

[AlphaFrog]

That line of code is not in the code I suggested. You have some other code?

 

[Keith24249]

Hi,

Sorry! Yes there is some code that increments the unique number by 1 when I open the workbook template. The code is as follows:

 Private Sub Workbook_Open()
    Worksheets("QUOTE").Range("H2") = Worksheets("DATA").Cells(2, "E").Value
    Worksheets("DATA").Cells(2, "E").Value = Worksheets("DATA").Cells(2, "E").Value + 1
End Sub

Regards
Keith

 

[AlphaFrog]

This will ignore the error if worksheet Data is deleted.

Private Sub Workbook_Open()
    [COLOR="Red"]On Error Resume Next[/COLOR]
    Worksheets("QUOTE").Range("H2") = Worksheets("DATA").Cells(2, "E").Value
    Worksheets("DATA").Cells(2, "E").Value = Worksheets("DATA").Cells(2, "E").Value + 1
End Sub

 

[Keith24249]

Hi AlphaFrog,

This has got rid of the error message and allows me to re-open the saved as copy as many times as I like without the increment. However when I re-open the template it hasn't incremented to the next number and opens with the last saved as number. Hope I have explained this concisely enough
Regards
Keith