Sub SauvegardeXLSM()
Dim chemin$
chemin = ThisWorkbook.Path & "\Sauvegarde\" 'à adapter
If Dir(chemin, vbDirectory) = "" Then MkDir chemin 'création du sous-dossier
ThisWorkbook.SaveCopyAs chemin & ThisWorkbook.Name
End Sub
Sub SauvegardeXLSX()
If IsError(Application.Caller) Then Exit Sub 'sécurité
Dim chemin$, nom$
chemin = ThisWorkbook.Path & "\Sauvegarde\" 'à adapter
If Dir(chemin, vbDirectory) = "" Then MkDir chemin 'création du sous-dossier
ThisWorkbook.SaveCopyAs chemin & "µµµ.xlsm"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
nom = ThisWorkbook.Name
nom = Left(nom, InStrRev(nom, ".")) & "xlsx" 'nom avec extension xlsx
Workbooks(nom).Close 'sécurité
With Workbooks.Open(chemin & "µµµ.xlsm") 'ouverture du fichier sauvegardé
.ActiveSheet.Shapes(Application.Caller).Delete 'suppession du bouton
.SaveAs chemin & nom, 51 'enregistrement au format xlsx (sans macros)
.Close
End With
Kill chemin & "µµµ.xlsm" 'suppression du fichier xlsm
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [H5]) Is Nothing Or Not IsDate([H5]) Then Exit Sub
Dim t, d As Object, i&, col As Variant
If FilterMode Then ShowAllData 'si la feuille est filtrée
t = Range("A14:A" & Range("A" & Rows.Count).End(xlUp).Row + 13).Resize(, 10)
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(t) - 13
If t(i, 1) <> "" Then d(t(i, 1)) = t(i, 10) 'mémorisation de la valeur en colonne J
Next
With Sheets("Consommations Mensuelles")
If .FilterMode Then .ShowAllData 'si la feuille est filtrée
col = Application.Match([H5], .Rows(3), 0)
If IsNumeric(col) Then
t = .Range("A5:A" & .Range("A" & .Rows.Count).End(xlUp).Row + 4).Resize(, col)
For i = 1 To UBound(t) - 4
t(i, col) = d(t(i, 1))
Next
If i > 1 Then .Cells(5, col).Resize(i - 1) = Application.Index(t, , col) 'restitution
End If
.Activate 'facultatif
End With
End Sub
=SI(NB(C5:N5)<3;"";MOYENNE(INDEX(A5:N5;GRANDE.VALEUR(SI(ESTNUM(C5:N5);COLONNE(C5:N5));1));INDEX(A5:N5;GRANDE.VALEUR(SI(ESTNUM(C5:N5);COLONNE(C5:N5));2));INDEX(A5:N5;GRANDE.VALEUR(SI(ESTNUM(C5:N5);COLONNE(C5:N5));3))))