Aide pour simplifier mon code

creolia · 13 Mars 2010

Bonjour au forum

je suis confronter a un petit probleme j'ai un code qui me permet d'ecrire sur mes pages aux meme lignes et les meme colonnes

Code:

With Worksheets("FORMATION2")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

le soucis ayant 71 onglets j'ai dupliquer ce code 71 fois sa fonctionne mais c'est tres long

Code:

With Worksheets("FORMATION2")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION3")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION4")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION5")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION6")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION7")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION8")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION9")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION10")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION11")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION12")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION13")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION14")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION15")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION16")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION17")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION18")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION19")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION20")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION21")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION22")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION23")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION24")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION25")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION26")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION27")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION28")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION29")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION30")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION31")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION32")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION33")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION34")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION35")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION36")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION37")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION38")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION39")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION40")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION41")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION42")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION43")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION44")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION45")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION46")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION47")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION48")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION49")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION50")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION51")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION52")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION40")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION53")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION54")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION55")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION56")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION57")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION58")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION59")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION60")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION61")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION62")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION63")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION64")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION65")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION66")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION67")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION68")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION69")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With
With Worksheets("FORMATION70")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

With Worksheets("FORMATION71")
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
  
End With

je me demandais si il y avais une solution pour reduire le temps d'attente lors de l'execution en simple ameliorer ce code je n'ai pas mis de fichier joint car 71 onglet sa fais un peut lourd et si j'en enleve le probleme se posseras plus

à savoir que les onglets se suive (FORMATION2,FORMATION3,..........FORMATION71
merci pour votre aide

et bon week end

Dull · 13 Mars 2010

Re : Aide pour simplifier mon code

Salut Créolia, le Forum

pas sur d'avoir compris mais essaye ça

Code:

Dim i As Byte
For i = 2 To 71
With Worksheets("FORMATION") & i
DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
  TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
  .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
  .Cells(DernLig, 2) = TextBoxNom
  .Cells(DernLig, 3) = TextBoxPrenom
  .Cells(DernLig, 4) = TextBoxService
End With
Next i

A Tester

Bonne Journée

creolia · 13 Mars 2010

Re : Aide pour simplifier mon code

Re bonjour et merci pour on aide donc j'ai essayer ton code sauf qu'il me dit que sa n'apartient pas a la selection

vue la grosseur du fichier j'ai mis sur cijoint ils est assez touffut mais comme j'ai dit j'essais de simplifier le tout petit à petit vue mon peut d'experience

le fichier

pour ce faire a la page accueil lancer USF cliquer sur nouveau renseigner les textbox puis valider et la j'ai l'erreur

Cijoint.fr - Service gratuit de dépôt de fichiers

Roland_M · 13 Mars 2010

Re : Aide pour simplifier mon code

bonjour

essai comme ceci

Code:

Dim I As Integer, F$
For I = 1 To ThisWorkbook.Sheets.Count
 F$ = "FORMATION" & I
 If Sheets(I).Name = F$ Then
    With Worksheets(F$)
      DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
      TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
     .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
     .Cells(DernLig, 2) = TextBoxNom
     .Cells(DernLig, 3) = TextBoxPrenom
     .Cells(DernLig, 4) = TextBoxService
    End With
  End If
Next

creolia · 13 Mars 2010

Re : Aide pour simplifier mon code

bonjour et Merci Roland sa fonctionne malheureusement sa me fais pas gagner du temps en meme temps il inscrit sur 71 pages c'est pas etonant pas grave

creolia · 13 Mars 2010

Re : Aide pour simplifier mon code

j'avais déjà poser la question mais malheureusement j'avais pas eu de réponse je reessais aujourd'hui

es ce possible avec un code de vba ET NON une formule mettre une égalité entre mes 4 première colonne de ma feuille FORMATION2 et LES 71 AUTRE PAGES je me disais sa serais mieux pour arrivée au résultat voulut au dessus .

merci pour vos lumière à bientot

Fo_rum · 13 Mars 2010

Re : Aide pour simplifier mon code

Salut

tu ne dis pas si les onglets ont tous la même dernière ligne.
A tester dans ce cas :

Code:

Private Sub CommandButton1_Click()
  With Worksheets("FORMATION2")
    DernLig = .Cells(.Columns(1).Cells.Count, 1).End(xlUp).Row + 1
    TextBoxNoOrdre = Application.WorksheetFunction.Max(.Range("A5:A" & DernLig)) + 1
    .Cells(DernLig, 1) = CLng(TextBoxNoOrdre)
    .Cells(DernLig, 2) = TextBoxNom
    .Cells(DernLig, 3) = TextBoxPrenom
    .Cells(DernLig, 4) = TextBoxService
  End With
  [COLOR="DarkGreen"]'copie les 4 cellules dans les onglets suivants[/COLOR]
  For n = 3 To 71
    Range(Cells(DernLig, "A"), Cells(DernLig, "D")).Copy Sheets("FORMATION" & n).Cells(DernLig, "A")
  Next
End Sub

tu as encore de sacrées simplifications en vue !

creolia · 14 Mars 2010

Re : Aide pour simplifier mon code

bonjour Merci FO RUM mais il me fais une erreur de compilation projet ou bibliotheque introuvable

saurais tu pourquoi merci

a bientot

creolia · 14 Mars 2010

Re : Aide pour simplifier mon code

Re mea cool pas le ralentissement viendrais d'autre module et non a la suite de mes commande cité plus haut je vais faire une recherche de se coter merci a tous je vous tiens informer

Roland_M · 14 Mars 2010

Re : Aide pour simplifier mon code

bonjour

déjà, pourrais tu être un peu plus précis !
sur quelle ligne ça bug ?

as-tu étais voir si tu avais une référence manquante !
si c'est pour > Application.WorksheetFunction.Max(.Range("A5:A" & DernLig))
voir si ceci est coché Exp: > Microsoft Excel Objects 11.0 Library

les exemples ci-dessus fonctionnent très bien !
mais comme tu n'y piges rien, tu continues à taper à travers tout !
tu as déjà oublié mes conseils !?

bon courage quand même et bon week end

creolia · 15 Mars 2010

Re : Aide pour simplifier mon code

Non j'ai pas oublier des conseil je les applique meme comme j'ai dit plus haut le probleme venais pas du code mais une macro qui me ralentissais le tout apres differente reecherche et de test j'ai reussis a l'identifier et mon probleme a put etre resolu merci a tous pour votre aide la macro concernée servais a requadriller mes ligne et colonne je l'ai donc enlever et sa roule

a bientot et merci a tous encore

Effectuez une recherche sur Excel Downloads...

Aide pour simplifier mon code

creolia

XLDnaute Impliqué

Dull

XLDnaute Barbatruc

creolia

XLDnaute Impliqué

Roland_M

XLDnaute Barbatruc

creolia

XLDnaute Impliqué

creolia

XLDnaute Impliqué

Fo_rum

XLDnaute Accro

creolia

XLDnaute Impliqué

creolia

XLDnaute Impliqué

Roland_M

XLDnaute Barbatruc

creolia

XLDnaute Impliqué

Discussions similaires

Nous accordons de l'importance à votre vie privée