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 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
| Sub Choix_date()
On Error Resume Next
' sélection des données en fonction de la date
Dim A As Single
Dim B As String
Dim K As Worksheet
Dim L As Worksheet
Dim M As Range
Dim FirstFound2 As String
Dim Plg1 As Range
Dim Plg2 As Range
Dim EcartNeg As Integer
A = InputBox("Date d'enregistrement?", "Choix Date") 'quelle date choisir
If A <> "" Then A = CDate(A)
Set K = Worksheets("Mafeuille") ' On travaille sur la feuille Mafeuille chèques
Set Plg1 = K.Range(K.Cells(1, 1), K.Cells(1000, 1))
With Plg1
Set M = .Find(A, LookIn:=xlValues, LookAt:=xlPart)
If Not (M Is Nothing) Then
FirstFound2 = M.Address
Do
M.Offset(0, 15).Value = "OK !"
Set M = .FindNext(M)
Loop While M.Address <> FirstFound2
Else
MsgBox "Cette date n'est pas répertorié", vbOKOnly, "Mafeuille"
End If
End With
Dim shtoto As Worksheet 'création de la feuille temporaire pour les données sélectionnées selon la date
Set shname = Sheets.Add(After:=Sheets(Sheets.Count))
Application.DisplayAlerts = False
For Each X In Sheets
If X.Name = A Then X.Delete
Next
Application.DisplayAlerts = True
shname.Name = "Date_" + A
Dim iLI As Long
Dim iRE As Long
Dim iRA As Long
Dim iLO As Long
Dim LI As Worksheet
Dim LO As Worksheet
Dim RE As Worksheet
Dim RA As Worksheet
Set LI = Worksheets("Mafeuille")
Set RE = Worksheets("Date_" + A)
iRE = 3
iRA = 3
For iLI = 2 To 1000
If LI.Cells(iLI, 17).Text = "OK !" Then
LI.Range(iLI & ":" & iLI).Copy RE.Cells(iRE, 1)
iRE = iRE + 1
End If
Next
For iLO = 2 To 1000
If LO.Cells(iLO, 17).Text = "OK !" Then
LO.Range(iLO & ":" & iLO).Copy RA.Cells(iRA, 1)
iRA = iRA + 1
End If
Next
End sub |
Partager