Save scenario. Need assistance.

[Rudi]

Hi,

See the code below:
I am saving a file and then deleting the panel sheet from the saved file. However, after the file is saved, I need to clear the source data from the sheet "data" in the original file. Each time I save as, the original file becomes the saved file with the new name. Do I need to reopen the original file to clear the data sheet or is there some trick involved that I am overlooking. TX. (I cannot clear the data sheet before the file is saved, else the new saved file have not data on the data sheet.

Scenario:
I need to save the original file with a cleared out data sheet
The new created file must have the data still on the data sheet, but the panel sheet must be deleted.

TX

Code: Select all

Sub Test()
    Dim sSave As String
    Dim sFN As String
    Beep
    sSave = MsgBox("The data has been formatted and transferred. Do you want to save the workbook now?", vbQuestion + vbYesNo)
    If sSave = vbYes Then
        sFN = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Save As")
        If sFN = "False" Then Exit Sub
        ActiveWorkbook.SaveAs Filename:=sFN, FileFormat:=52
        Application.DisplayAlerts = False
        ActiveWorkbook.Sheets("panel").Delete
        Application.DisplayAlerts = True
        ThisWorkbook.Sheets("data").UsedRange.Clear '<<< This must clear the Test.xlsm Data sheet; not the current workbook Data sheet
    Else
        MsgBox "This workbook has not yet been saved!", vbExclamation
    End If
End Sub

[HansV]

With the code you use, you'd have to reopen Test.xlsm, clear the Data sheet, then save and close it.
Alternatively, you could use

ActiveWorkbook.SaveCopyAs Filename:=sFN

You'd have to open the copy, delete the Panel sheet, then save and close it, and finally clear the Data sheet from ThisWorkbook (which is still Test.xlsm in this case).

So it's much of a sameness.

[Rudi]

Tx for confirmation...