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
| Sub Actualiser2()
Dim c As Range, v As Range, y As Range
Dim NewLig As Long
Application.ScreenUpdating = False
Set Sh = ThisWorkbook.Sheets("std")
LastLig1 = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).Row
Dim valeur As String
valeur = InputBox("Entrée période", "Choix de la période")
If valeur <> "" Then
Application.ScreenUpdating = False
With Workbooks("2010 STD Activities status.xls").Sheets("2010")
.AutoFilterMode = False
LastLig2 = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("A4:X" & LastLig2)
.AutoFilter field:=17, Criteria1:=valeur
.AutoFilter field:=24, Criteria1:=">0"
.AutoFilter field:=9, Criteria1:="STD"
End With
If .Range("A4:A" & LastLig2).SpecialCells(xlCellTypeVisible).Count > 0 Then
For Each v In .Range("A5:A" & LastLig2).SpecialCells(xlCellTypeVisible)
If v.Value <> "" Then
Set Sh = ThisWorkbook.Sheets("std")
Set c = Sh.Range("A2:A" & LastLig1).Find(v.Value, LookIn:=xlValues, lookat:=xlWhole)
NewLig = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).Row + 1
For Each c In .Range("A5:A" & LastLig2).SpecialCells(xlCellTypeVisible)
Sh.Cells(NewLig, 1).Value = .Cells(c.Row, 2).Value
Sh.Cells(NewLig, 2).Value = .Cells(c.Row, 7).Value
Sh.Cells(NewLig, 3).Value = .Cells(c.Row, 8).Value
Sh.Cells(NewLig, 6).Value = .Cells(c.Row, 10).Value
Sh.Cells(NewLig, 8).Value = .Cells(c.Row, 12).Value
Sh.Cells(NewLig, 17).Value = .Cells(c.Row, 17).Value
Sh.Cells(NewLig, 13).Value = .Cells(c.Row, 24).Value
Sh.Cells(NewLig, 9).Value = .Cells(c.Row, 9).Value
Sh.Cells(NewLig, 10).Value = .Cells(c.Row, 29).Value
Sh.Cells(NewLig, 4).Value = UCase(Sh.Cells(NewLig, 1).Value) & UCase(Sh.Cells(NewLig, 2).Value)
Sh.Cells(NewLig, 4).Value = Replace(Cells(NewLig, 3).Value, " ", "")
NewLig = NewLig + 1
If Not c Is Nothing Then
v.Resize(1, 24).Copy c
Set c = Nothing
Else
LastLig1 = LastLig1 + 1
v.Resize(1, 24).Copy Sh.Range("A" & LastLig1)
End If
Next c
End If
Next v
End If
.AutoFilterMode = False
End With
End If
Set Sh = Nothing
End Sub |
Partager