Mise à jour d'une macro
Bonjour,
Je fais appelle à vous, car j'ai un petit souci avec une de mes macros.
Voila j'ai créer un classeur avec plusieurs pages.
Avec l'aide du forum j'ai créer une macro qui me permets d'actualiser mes données. Jusque-là tout marche parfaitement.
Depuis, J'ai créer une macro pour ajouter une ligne dans mes feuilles. le Ik c'est que ma première macro qui actualise mon classeur n'actualise pas les nouvelles lignes créées.
Pouvez-vous m'aider ? Existe-t-il un code pour mettre à jour ma première macro si des nouvelles lignes sont créer ?
En vous remerciant par avance
Windows / Edge 108.0.1462.54
4 réponses
-
Bonjour
Oui, pour mettre à jour ta macro tu peux utiliser la méthode
CalculateFullde l'objetApplicationd'Excel, ça va recalculer toutes les formules du classeur y compris les nouvelles lignes ajoutées. -
Bonjour,
Je pense que vous devriez en premier lieu appeler la macro qui ajoute une ligne et ensuite appeler la macro qui enregistre. Ou encore créer une nouvelle macro qui ajouté une ligne puis enregistre.
-
Contributeur
bonjour,
peux-tu nous montrer le code de la première macro?
veille à utiliser ceci: https://codes-sources.commentcamarche.net/faq/11288-poster-un-extrait-de-code-
Private Sub zerovide(rg As Range) Dim cl As Range For Each cl In rg If cl = "" Then cl = 0 End If Next cl End Sub Sub makro() If MsgBox("Etes-vous sûr de vouloir mettre à jour votre fichier?", vbInformation + vbYesNo, "Demande de confirmation") = vbYes Then oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Merci de patienter ..." Call zerovide(Sheets("JANV").[D5:AH16,D18:AH33,D35:AH38,D40:AH57]) Call zerovide(Sheets("FEV").[D5:AE16,D18:AE33,D35:AE38,D40:AE57]) Call zerovide(Sheets("MARS").[D5:AH16,D18:AH33,D35:AH38,D40:AH57]) Call zerovide(Sheets("AVRIL").[D5:AG16,D18:AG33,D35:AG38,D40:AG57]) Call zerovide(Sheets("MAI").[D5:AH16,D18:AH33,D35:AH38,D40:AH57]) Call zerovide(Sheets("JUIN").[D5:AG16,D18:AG33,D35:AG38,D40:AG57]) Call zerovide(Sheets("JUIL").[D5:AH16,D18:AH33,D35:AH38,D40:AH57]) Call zerovide(Sheets("AOUT").[D5:AH16,D18:AH33,D35:AH38,D40:AH57]) Call zerovide(Sheets("SEPT").[D5:AG16,D18:AG33,D35:AG38,D40:AG57]) Call zerovide(Sheets("OCT").[D5:AH16,D18:AH33,D35:AH38,D40:AH57]) Call zerovide(Sheets("NOV").[D5:AG16,D18:AG33,D35:AG38,D40:AG57]) Call zerovide(Sheets("DEC").[D5:AH16,D18:AH33,D35:AH38,D40:AH57]) Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar MsgBox ("Votre fichier a été mis à jour !") End If End SubMerci pour votre retour.
Ci-dessus le code de la première macro qui remplit mon classeur.
Ci-dessous un code pour ajouter une ligne, avec un autre code pour mettre à jour les données si besoin. Je débute en VBA donc j'avance petit à petit.
Si vous avez des suggestion pour améliorer le code je suis preneuse.
Merci d'avance
Sub AjouterAsso_CULTURE() ' ' AjouterAsso_CULTURE Macro ' On Error Resume Next Application.ScreenUpdating = False ' If MsgBox("Etes-vous sûr de vouloir ajouter une association dans la rubrique culture?", vbInformation + vbYesNo, "Demande de confirmation") = vbYes Then Sheets(Array("ASSO", "JANV", "FEV", "MARS", "AVRIL", "MAI", "JUIN", "JUIL", "AOUT", _ "SEPT", "OCT", "NOV", "DEC", "RECAP - Nbr COPIES", "RECAP - COMPTA")).Select Sheets("ASSO").Activate Rows("17:17").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Sheets("ASSO").Select Range("A17").Select ActiveCell.FormulaR1C1 = "NOUVELLE ASSO" Sheets("JANV").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Range("AK16").Select Selection.AutoFill Destination:=Range("AK16:AK17"), Type:=xlFillDefault Range("AK16:AK17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("FEV").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AF16").Select Selection.AutoFill Destination:=Range("AF16:AF17"), Type:=xlFillDefault Range("AF16:AF17").Select Range("AG16").Select Selection.AutoFill Destination:=Range("AG16:AG17"), Type:=xlFillDefault Range("AG16:AG17").Select Range("AH16").Select Selection.AutoFill Destination:=Range("AH16:AH17"), Type:=xlFillDefault Range("AH16:AH17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("MARS").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Range("AK16").Select Selection.AutoFill Destination:=Range("AK16:AK17"), Type:=xlFillDefault Range("AK16:AK17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("AVRIL").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AH16").Select Selection.AutoFill Destination:=Range("AH16:AH17"), Type:=xlFillDefault Range("AH16:AH17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("MAI").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Range("AK16").Select Selection.AutoFill Destination:=Range("AK16:AK17"), Type:=xlFillDefault Range("AK16:AK17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("JUIN").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AH16").Select Selection.AutoFill Destination:=Range("AH16:AH17"), Type:=xlFillDefault Range("AH16:AH17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("JUIL").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Range("AK16").Select Selection.AutoFill Destination:=Range("AK16:AK17"), Type:=xlFillDefault Range("AK16:AK17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("AOUT").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Range("AK16").Select Selection.AutoFill Destination:=Range("AK16:AK17"), Type:=xlFillDefault Range("AK16:AK17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("SEPT").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AH16").Select Selection.AutoFill Destination:=Range("AH16:AH17"), Type:=xlFillDefault Range("AH16:AH17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("OCT").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Range("AK16").Select Selection.AutoFill Destination:=Range("AK16:AK17"), Type:=xlFillDefault Range("AK16:AK17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("NOV").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AH16").Select Selection.AutoFill Destination:=Range("AH16:AH17"), Type:=xlFillDefault Range("AH16:AH17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("DEC").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("AI16").Select Selection.AutoFill Destination:=Range("AI16:AI17"), Type:=xlFillDefault Range("AI16:AI17").Select Range("AJ16").Select Selection.AutoFill Destination:=Range("AJ16:AJ17"), Type:=xlFillDefault Range("AJ16:AJ17").Select Range("AK16").Select Selection.AutoFill Destination:=Range("AK16:AK17"), Type:=xlFillDefault Range("AK16:AK17").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("RECAP - Nbr COPIES").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("D16").Select Selection.AutoFill Destination:=Range("D16:D17"), Type:=xlFillDefault Range("D16:D17").Select Range("E16").Select Selection.AutoFill Destination:=Range("E16:E17"), Type:=xlFillDefault Range("E16:E17").Select Range("F16").Select Selection.AutoFill Destination:=Range("F16:F17"), Type:=xlFillDefault Range("F16:F17").Select Range("G16").Select Selection.AutoFill Destination:=Range("G16:G17"), Type:=xlFillDefault Range("G16:G17").Select Range("H16").Select Selection.AutoFill Destination:=Range("H16:H17"), Type:=xlFillDefault Range("H16:H17").Select Range("I16").Select Selection.AutoFill Destination:=Range("I16:I17"), Type:=xlFillDefault Range("I16:I17").Select Range("J16").Select Selection.AutoFill Destination:=Range("J16:J17"), Type:=xlFillDefault Range("J16:J17").Select Range("K16").Select Selection.AutoFill Destination:=Range("K16:K17"), Type:=xlFillDefault Range("K16:K17").Select Range("L16").Select Selection.AutoFill Destination:=Range("L16:L17"), Type:=xlFillDefault Range("L16:L17").Select Range("M16").Select Selection.AutoFill Destination:=Range("M16:M17"), Type:=xlFillDefault Range("M16:M17").Select Range("N16").Select Selection.AutoFill Destination:=Range("N16:N17"), Type:=xlFillDefault Range("N16:N17").Select Range("O16").Select Selection.AutoFill Destination:=Range("O16:O17"), Type:=xlFillDefault Range("O16:O17").Select Range("P16").Select Selection.AutoFill Destination:=Range("P16:P17"), Type:=xlFillDefault Range("P16:P17").Select Range("D18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("E18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("F18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("G18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("H18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("I18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("J18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-2]C)" Range("J18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("K18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("L18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("M18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("N18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("O18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("P18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("P19").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Sheets("RECAP - COMPTA").Select Range("A16").Select Selection.AutoFill Destination:=Range("A16:A17"), Type:=xlFillDefault Range("A16:A17").Select Range("B16").Select Selection.AutoFill Destination:=Range("B16:B17"), Type:=xlFillDefault Range("B16:B17").Select Range("C16").Select Selection.AutoFill Destination:=Range("C16:C17"), Type:=xlFillDefault Range("C16:C17").Select Range("D16").Select Selection.AutoFill Destination:=Range("D16:D17"), Type:=xlFillDefault Range("D16:D17").Select Range("E16").Select Selection.AutoFill Destination:=Range("E16:E17"), Type:=xlFillDefault Range("E16:E17").Select Range("F16").Select Selection.AutoFill Destination:=Range("F16:F17"), Type:=xlFillDefault Range("F16:F17").Select Range("G16").Select Selection.AutoFill Destination:=Range("G16:G17"), Type:=xlFillDefault Range("G16:G17").Select Range("H16").Select Selection.AutoFill Destination:=Range("H16:H17"), Type:=xlFillDefault Range("H16:H17").Select Range("I16").Select Selection.AutoFill Destination:=Range("I16:I17"), Type:=xlFillDefault Range("I16:I17").Select Range("J16").Select Selection.AutoFill Destination:=Range("J16:J17"), Type:=xlFillDefault Range("J16:J17").Select Range("K16").Select Selection.AutoFill Destination:=Range("K16:K17"), Type:=xlFillDefault Range("K16:K17").Select Range("L16").Select Selection.AutoFill Destination:=Range("L16:L17"), Type:=xlFillDefault Range("L16:L17").Select Range("M16").Select Selection.AutoFill Destination:=Range("M16:M17"), Type:=xlFillDefault Range("M16:M17").Select Range("N16").Select Selection.AutoFill Destination:=Range("N16:N17"), Type:=xlFillDefault Range("N16:N17").Select Range("B18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("C18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("D18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("E18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("F18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("G18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("H18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("I18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("J18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("K18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("L18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("M18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)" Range("N18").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])" Range("N19").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With MsgBox "La nouvelle association à été créée !" End If End SubSub MAJ_CULTURE() ' ' MAJ_CULTURE Macro ' If MsgBox("Etes-vous sûr de vouloir mettre à jour votre rubrique?", vbInformation + vbYesNo, "Demande de confirmation") = vbYes Then Sheets("RECAP - Nbr COPIES").Select Range("D17").Select Selection.AutoFill Destination:=Range("D17:D18"), Type:=xlFillDefault Range("D17:D18").Select Range("E17").Select Selection.AutoFill Destination:=Range("E17:E18"), Type:=xlFillDefault Range("E17:E18").Select Range("F17").Select Selection.AutoFill Destination:=Range("F17:F18"), Type:=xlFillDefault Range("F17:F18").Select Range("G17").Select Selection.AutoFill Destination:=Range("G17:G18"), Type:=xlFillDefault Range("G17:G18").Select Range("H17").Select Selection.AutoFill Destination:=Range("H17:H18"), Type:=xlFillDefault Range("H17:H18").Select Range("I17").Select Selection.AutoFill Destination:=Range("I17:I18"), Type:=xlFillDefault Range("I17:I18").Select Range("J17").Select Selection.AutoFill Destination:=Range("J17:J18"), Type:=xlFillDefault Range("J17:J18").Select Range("K17").Select Selection.AutoFill Destination:=Range("K17:K18"), Type:=xlFillDefault Range("K17:K18").Select Range("L17").Select Selection.AutoFill Destination:=Range("L17:L18"), Type:=xlFillDefault Range("L17:L18").Select Range("M17").Select Selection.AutoFill Destination:=Range("M17:M18"), Type:=xlFillDefault Range("M17:M18").Select Range("N17").Select Selection.AutoFill Destination:=Range("N17:N18"), Type:=xlFillDefault Range("N17:N18").Select Range("O17").Select Selection.AutoFill Destination:=Range("O17:O18"), Type:=xlFillDefault Range("O17:O18").Select Range("P17").Select Selection.AutoFill Destination:=Range("P17:P18"), Type:=xlFillDefault Range("P17:P18").Select Range("D19").Select Sheets("RECAP - COMPTA").Select Range("B17").Select Selection.AutoFill Destination:=Range("B17:B18"), Type:=xlFillDefault Range("B17:B18").Select Range("C17").Select Selection.AutoFill Destination:=Range("C17:C18"), Type:=xlFillDefault Range("C17:C18").Select Range("D17").Select Selection.AutoFill Destination:=Range("D17:D18"), Type:=xlFillDefault Range("D17:D18").Select Range("E17").Select Selection.AutoFill Destination:=Range("E17:E18"), Type:=xlFillDefault Range("E17:E18").Select Range("F17").Select Selection.AutoFill Destination:=Range("F17:F18"), Type:=xlFillDefault Range("F17:F18").Select Range("G17").Select Selection.AutoFill Destination:=Range("G17:G18"), Type:=xlFillDefault Range("G17:G18").Select Range("H17").Select Selection.AutoFill Destination:=Range("H17:H18"), Type:=xlFillDefault Range("H17:H18").Select Range("I17").Select Selection.AutoFill Destination:=Range("I17:I18"), Type:=xlFillDefault Range("I17:I18").Select Range("J17").Select Selection.AutoFill Destination:=Range("J17:J18"), Type:=xlFillDefault Range("J17:J18").Select Range("K17").Select Selection.AutoFill Destination:=Range("K17:K18"), Type:=xlFillDefault Range("K17:K18").Select Range("L17").Select Selection.AutoFill Destination:=Range("L17:L18"), Type:=xlFillDefault Range("L17:L18").Select Range("M17").Select Selection.AutoFill Destination:=Range("M17:M18"), Type:=xlFillDefault Range("M17:M18").Select Range("N17").Select Selection.AutoFill Destination:=Range("N17:N18"), Type:=xlFillDefault Range("N17:N18").Select Range("N19").Select MsgBox "Votre rubrique à été mis à jour !" End If End Sub -
Contributeur@Alice7684
Je vois dans le code des éléments tels que
[D5:AH16,D18:AH33,D35:AH38,D40:AH57]
Cela a du sens tant que rien ne change. Le plus souvent, il est préférable que le code analyse les données, et découvre quelles sont les groupes de cellules à traiter.
Par exemple, tu déciderais qu'il faut traiter les colonnes de D à AH de toutes les lignes ayant certaines caractéristiques.
-
@yg_be
Merci pour ton retour.
Je ne suis pas sur de comprendre.
Peux tu me donner un exemple pour ce code ?
Call zerovide(Sheets("JANV").[D5:AH16,D18:AH33,D35:AH38,D40:AH57])Merci :)
-
Contributeur@Alice7684
Ce serait plutôt à toi d'expliquer pourquoi tu as choisi ces lignes, et si on ne pourrait pas plutôt automatiser ce choix.
-
Contributeur@Alice7684
Je pense que tu as commencé à construire une solution sans prendre en compte la globalité de la situation.
A moins que tu ne nous expliques, globalement, ce que tu essaies d'obtenir, je ne pense pas que nous pourrons réellement t'aider.
-
-
Ou écrire une procédure en vba. Cette procédure devra vérifier si une ligne à été ajoutée (EOF, (End Of File) ) je pense qu'il faut faire count.range+i autrement dit compter les lignes, mettre le résultat dans une variable puis Incrementer cette variable de 1.si Oui alors , sauvegarder . Si non , demander pour ajouter une ligne ou autre chose.
