Sujet Précédent Sujet Suivant

Mise à jour d'une macro

Fermé
Alice7684 - 8 janv. 2023 à 18:55
yg_be Messages postés 22793 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 18 mai 2024 - 14 janv. 2023 à 20:40

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

A voir également:

4 réponses

Réponse 1 / 4
xHaMaz Messages postés 108 Date d'inscription mardi 3 janvier 2023 Statut Membre Dernière intervention 23 avril 2023 14
8 janv. 2023 à 19:30

Bonjour

Oui, pour mettre à jour ta macro tu peux utiliser la méthode CalculateFull de l'objet Application d'Excel, ça va recalculer toutes les formules du classeur y compris les nouvelles lignes ajoutées.
0
Réponse 2 / 4
blackmefias_3350 Messages postés 706 Date d'inscription dimanche 20 septembre 2020 Statut Membre Dernière intervention 8 mars 2024 58
8 janv. 2023 à 19:31

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. 
0
Réponse 3 / 4
yg_be Messages postés 22793 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 18 mai 2024 1 467
8 janv. 2023 à 19:50

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
0
Alice7684
9 janv. 2023 à 01:38 
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 Sub

Merci 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 Sub
Sub 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
0
yg_be Messages postés 22793 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 18 mai 2024 1 467 > Alice7684
9 janv. 2023 à 08:55

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.

0
Alice7684 > yg_be Messages postés 22793 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 18 mai 2024
10 janv. 2023 à 10:24

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 :)

0
yg_be Messages postés 22793 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 18 mai 2024 1 467 > Alice7684
10 janv. 2023 à 12:09

Ce serait plutôt à toi d'expliquer pourquoi tu as choisi ces lignes, et si on ne pourrait pas plutôt automatiser ce choix.

0
yg_be Messages postés 22793 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 18 mai 2024 1 467 > Alice7684
14 janv. 2023 à 20:40

si la variable irow contient le n° de ligne créée, tu peux adresser ainsi la cellule en colonne B de cette ligne:

Sheets("ADMINISTRES").cells(irow,"B") = _
    InputBox("Entrez le Prénom", "Nouvelle Association Culture", "ENTREZ LE PRENOM ICI")

ou, si tu es certaine qu'il est superflu de préciser la feuille:

cells(irow,"B") = _
    InputBox("Entrez le Prénom", "Nouvelle Association Culture", "ENTREZ LE PRENOM ICI")
1
Réponse 4 / 4
blackmefias_3350 Messages postés 706 Date d'inscription dimanche 20 septembre 2020 Statut Membre Dernière intervention 8 mars 2024 58
8 janv. 2023 à 19:54

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. 
0

Discussions similaires

mise à jour boitier X96 mini
Brunoche -
Vico -

3 réponses
Mise à jour impossible à terminer
jeannets -
Salv -

27 réponses