For li = 15 To 1000 Step 3 'pour autant de lignes If Sheets(Oldname).Range("D" & li).Value = "" Then Else Sheets(Newname).Range("F" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("F" & li).Value Sheets(Newname).Range("G" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("G" & li).Value Sheets(Newname).Range("H" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("H" & li).Value Sheets(Newname).Range("I" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("I" & li).Value Sheets(Newname).Range("J" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("J" & li).Value Sheets(Newname).Range("K" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("K" & li).Value Sheets(Newname).Range("L" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("L" & li).Value End If Next li
Private Sub Ajouterpage_QuandClic() 'Ajoute un page et prépare pour l'encodage Dim Newname As String 'Nom de la feuille ajoutée Dim Oldname As String 'Nom de la feuille d'origine Dim li As Integer 'Index de ligne Application.ScreenUpdating = False Oldname = ActiveSheet.Name 'Copie ActiveSheet.Copy after:=Sheets(Sheets.Count) Newname = ActiveSheet.Name 'Modification de la copie '--------- Indexation et nom de la feuille Sheets(Newname).Select Range("O1").Value = Range("O1").Value + 1 Range("P12").Value = Sheets(Oldname).Range("IV12").Value + 1 ActiveSheet.Name = Range("F3").Value & " - " & Range("O1").Value Newname = ActiveSheet.Name '--------- Effacement des données Sheets(Newname).Select Range("P15:IV1000").ClearContents '--------- Incrémentation des formules For li = 15 To 1000 'pour autant de lignes Sheets(Newname).Range("F" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("F" & li).Value Sheets(Newname).Range("G" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("G" & li).Value Sheets(Newname).Range("H" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("H" & li).Value Sheets(Newname).Range("I" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("I" & li).Value Sheets(Newname).Range("J" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("J" & li).Value Sheets(Newname).Range("K" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("K" & li).Value Sheets(Newname).Range("L" & li).FormulaLocal = "=NB.SI($P" & li & ":$IV" & li & ";F$12)+" & Sheets(Oldname).Range("L" & li).Value Next li 'Application.EnableEvents = True Application.ScreenUpdating = True MsgBox ActiveSheet.Name & " créée !" End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question