1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| Sub creation_fiche()
Dim DerLigne As Long, i As Long
Dim Ws As Worksheet
Dim Feuil As String
Dim Shp As Shape
Application.ScreenUpdating = False
With Worksheets("Liste")
DerLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 6 To DerLigne
Feuil = " Candidat " & .Cells(i, 1)
If Feuil <> "" Then
If FeuilleExiste(Feuil) Then
Set Ws = Worksheets(Feuil)
For Each Shp In Ws.Shapes
Shp.Delete
Next Shp
Else
Worksheets("Fiche candidat").Copy after:=Worksheets(Worksheets.Count)
Set Ws = ActiveSheet
Ws.Name = Feuil
End If
Ws.Range("K1") = .Cells(i, 1)
Ws.Range("C7") = .Cells(i, 2)
Ws.Range("C7") = .Cells(i, 3)
Ws.Range("C8") = .Cells(i, 4)
Ws.Range("I2") = .Cells(i, 5)
Ws.Range("D2") = .Cells(i, 6)
Ws.Range("C13") = .Cells(i, 7)
Ws.Range("C14") = .Cells(i, 8)
Ws.Range("C15") = .Cells(i, 9)
Ws.Range("I13") = .Cells(i, 10)
Ws.Range("I14") = .Cells(i, 11)
Ws.Range("I15") = .Cells(i, 12)
Ws.Range("B20") = .Cells(i, 13)
Ws.Range("G23") = .Cells(i, 14)
Ws.Range("B43") = .Cells(i, 15)
Ws.Range("I52") = .Cells(i, 18)
Ws.Range("D52") = .Cells(i, 19)
Ws.Range("C64") = .Cells(i, 23)
Ws.Range("C66") = .Cells(i, 24)
Ws.Range("C68") = .Cells(i, 25)
CopyImage .Cells(i, 16), Ws.Range("B28")
CopyImage .Cells(i, 17), Ws.Range("H28")
End If
Next i
End With
End Sub
Private Function FeuilleExiste(ByVal F As String) As Boolean
On Error Resume Next
FeuilleExiste = Worksheets(F).Index
End Function
Private Sub CopyImage(ByVal Img As String, ByVal Rng As Range)
On Error GoTo Fin
Worksheets("Liste").Shapes(Img).Copy
Rng.PasteSpecial
With Rng.Worksheet
With .Shapes(.Shapes.Count)
.Left = Rng.Left
.Top = Rng.Top
.Width = 4 * Rng.Width
.Height = 11 * Rng.Height
End With
End With
Fin:
End Sub |
Partager