VBA Save Backup whole workbook when close, then tab/worksheet

[HdCpr]

Hello All

Seen below Code on the forum , works perfectly for workbook but looking something that can only copy and backup on tab

Is there a similar code that will create a backup in the same way, but designate that only one tab should be included in the newly created file? As i have many different tabs in the workbook which saving large file.
I put below under this workbook in VBA not sure how to make it work for Sheet 1 or Sheet 2..... only

thanks a lot


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim MyBackupPath As String

MyBackupPath = "G:\2022\Backup File\"

ActiveWorkbook.SaveCopyAs MyBackupPath & Format(Now, "yy.mm.dd h mm a/p") & " " & Application.UserName & " " & ActiveWorkbook.Name


End Sub

 

[HighAndWilder]

Give this a go.

All you have to do is change this line and put in the name of the worksheet that you want to keep.

Wb.Worksheets("ToKeep").Copy Before:=ActiveWorkbook.Sheets(1)

I have not tested it on a G drive.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Wb As Workbook
Dim i As Integer
Dim strFilename As String

    ' Set a workbook object to the active workbook.
    Set Wb = ActiveWorkbook
    
    ' Define the backup filename.
    strFilename = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

    strFilename = "G:\2022\Backup File\" & Format(Now, "yy-mm-dd h-mm-ss a/p") & " " & _
        Application.UserName & " " & strFilename
        
    ' Create a new workbook.
    Workbooks.Add
    
    ' Copy the required worksheet to the new workbook.
    Wb.Worksheets("ToKeep").Copy Before:=ActiveWorkbook.Sheets(1)

    ' Turn off alerts.
    Application.DisplayAlerts = False
 
    ' Delete unrequired worksheets.
    For i = ActiveWorkbook.Sheets.Count To 2 Step -1
        ActiveWorkbook.Sheets(i).Delete
    Next i
    
    ' Save the new workbook.
    ActiveWorkbook.SaveAs Filename:=strFilename
    
    ' Close the new workbook.
    ActiveWorkbook.Close
    
    ' Close the main workbook.
    ActiveWorkbook.Save
    
    ' Turn on alerts.
    Application.DisplayAlerts = True
    
End Sub

 

[HighAndWilder]

I forgot to tell you that I have substituted the full stop, dot, period in the filename with a hyphen.

As this character has a specific meaning in file names I tend not to use it in the main body of the name.

 

[HdCpr]

Thank you so much . I will try this one on Monday and will update

much appreciated y

 

[HdCpr]

Thank you very much HighAndWilder , much appreciated
works as i wanted