Excel - A macro to backup a specific sheet

October 2016


I need some little help tweaking a macro.
Here's my code:
Sub Sauvegarder() 
Set Source = Union(Range("M17"), Range("M23")) 
If Application.CountA(Source) < 2 Then 
MsgBox "Entrez un nom de client et une numero de facture pour valider la sauvegarde" 
Dim nom As String 
    nom = Range("M17") & "_fact" & Range("M23") & "_" & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & ".xlsm" 
    ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & nom 
    rep = MsgBox("Votre base de données est sauvegardée sous le nom : " & nom, vbYes + vbInformation, "Copie sauvegarde classeur") 
If MsgBox("Voulez vous effacer les données ?", _ 
    vbYesNo, "Attention !") = vbYes Then Range("B3:B26,C3:C26,F3:F26,H3:H26,M17:P17,M19:P19,M21:P21,M23:P23,M25:P25,M27:P27,M29:P29,M30:P30,M31:P31,M32:P32,M33:P33").ClearContents 
End If 
End Sub

In this code I would like to backup only the "Invoice" sheet while maintaining the checks (and not the whole workbook).


- Copy the Invoice sheet to a new workbook,
- Save the new workbook,
- Close the workbook.
Dim Chemin As String 
Chemin = ActiveWorkbook.Path 
With ActiveWorkbook 
    .SaveAs Chemin & "\" & nom 
End With

Finally use this code.
ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & nom

Related :

This document entitled « Excel - A macro to backup a specific sheet » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.