fenec
XLDnaute Impliqué
Bonjour le forum
J’utilise cette macro pour archiver mes factures mais je rencontre un problème la cellule "I13" contient une formule que je ne souhaite par copier sur ma sauvegarde est ce possible que de ne copier que la valeur ?
D’avance merci
Cordialement
Fenec
Private Sub CommandButton3_Click() 'Archiver Factures
Application.ScreenUpdating = False
NomFichier = ActiveWorkbook.Name
défaut = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = défaut
nomfichier1 = ActiveWorkbook.Name
Me.Cells.Copy ActiveSheet.[A1]
ActiveSheet.Cells.Clear
With Me.Range("Zone_d_impression")
.Copy ActiveSheet.[B2]
ActiveSheet.[B2].Resize(.Rows.Count, .Columns.Count).Locked = True
ActiveSheet.PageSetup.PrintArea = "$B$2:$J$58"
With ActiveSheet.PageSetup
.RightHeader = "Page &P de &N"
.CenterFooter = _
"S.A.R.L au capital de "
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Zoom = 59
End With
ActiveSheet.Protect
ActiveSheet.Range("B6").Select
ChDir "C:\Users\Philippe\Mes documents\Archives\Factures"
fermer = Application.GetSaveAsFilename(ActiveSheet.Range("E13").Value & " " & Range("I14"), "Fichiers Excel,*.xls")
If fermer = False Then
Windows(nomfichier1).Activate
ActiveWorkbook.Close savechanges:=False
Exit Sub
End If
ActiveWorkbook.SaveAs Filename:=fermer
ActiveWorkbook.Close
NonClient = Range("E13")
num = Format(Val(Right(Range("I14"), 3)) + 1, "000")
ActiveSheet.Unprotect
Range("I2") = num
Range("Zone_a_remplir_Facture") = Empty
Range("I13").Select
Workbooks("Bons de Commande & Facture").Activate
ActiveWorkbook.Save
Application.ScreenUpdating = True
End With
End Sub
J’utilise cette macro pour archiver mes factures mais je rencontre un problème la cellule "I13" contient une formule que je ne souhaite par copier sur ma sauvegarde est ce possible que de ne copier que la valeur ?
D’avance merci
Cordialement
Fenec
Private Sub CommandButton3_Click() 'Archiver Factures
Application.ScreenUpdating = False
NomFichier = ActiveWorkbook.Name
défaut = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = défaut
nomfichier1 = ActiveWorkbook.Name
Me.Cells.Copy ActiveSheet.[A1]
ActiveSheet.Cells.Clear
With Me.Range("Zone_d_impression")
.Copy ActiveSheet.[B2]
ActiveSheet.[B2].Resize(.Rows.Count, .Columns.Count).Locked = True
ActiveSheet.PageSetup.PrintArea = "$B$2:$J$58"
With ActiveSheet.PageSetup
.RightHeader = "Page &P de &N"
.CenterFooter = _
"S.A.R.L au capital de "
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Zoom = 59
End With
ActiveSheet.Protect
ActiveSheet.Range("B6").Select
ChDir "C:\Users\Philippe\Mes documents\Archives\Factures"
fermer = Application.GetSaveAsFilename(ActiveSheet.Range("E13").Value & " " & Range("I14"), "Fichiers Excel,*.xls")
If fermer = False Then
Windows(nomfichier1).Activate
ActiveWorkbook.Close savechanges:=False
Exit Sub
End If
ActiveWorkbook.SaveAs Filename:=fermer
ActiveWorkbook.Close
NonClient = Range("E13")
num = Format(Val(Right(Range("I14"), 3)) + 1, "000")
ActiveSheet.Unprotect
Range("I2") = num
Range("Zone_a_remplir_Facture") = Empty
Range("I13").Select
Workbooks("Bons de Commande & Facture").Activate
ActiveWorkbook.Save
Application.ScreenUpdating = True
End With
End Sub