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
|
Private Sub CommandButton1_Click()
' Get a new revision number
Sheets("COMPLETE LIST").Visible = True
Sheets("COMPLETE LIST").Select
ActiveSheet.Unprotect
' Refresh complete list
ActiveSheet.Range("C7").AutoFilter Field:=1
ActiveSheet.Range("D7").AutoFilter Field:=2
ActiveSheet.Range("E7").AutoFilter Field:=3
ActiveSheet.Range("F7").AutoFilter Field:=4
ActiveSheet.Range("G7").AutoFilter Field:=5
ActiveSheet.Range("H7").AutoFilter Field:=6
' Filtering
Sheets("COMPLETE LIST").Select
Dim LastLig As Long
Dim Plage As Range
With Worksheets("COMPLETE LIST")
LastLig = .Cells(.Rows.Count, "C").End(xlUp).Row
If LastLig >= 8 Then
Set Plage = .Range("C9:FG" & LastLig)
' Suite de ton code concernant Plage (de type range)
ActiveSheet.Range("C9:FG" & LastLig).Select
Else
Set Plage = Nothing
End If
End With
Selection.Sort Key1:=ActiveSheet.Range("H9"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
CRITERIA = Application.InputBox("Sought Engine ?", "CRITERIA ?,Type:=1+2")
Sheets("COMPLETE LIST").Select
ActiveSheet.ListObjects("Plage").Range.AutoFilter Field:=1, Criteria1:=CRITERIA
CRITERIA = Application.InputBox("Sought Document ?", "CRITERIA ?", Type:=2)
Sheets("COMPLETE LIST").Select
ActiveSheet.ListObjects("Plage").Range.AutoFilter Field:=4, Criteria2:=CRITERIA
Selection.Sort Key1:=ActiveSheet.Range("H9"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
' Get revision number :
ActiveSheet.Range("G9").Copy
Sheets("NEW REVISION").Select
ActiveSheet.Range("Q3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L7") = Range("Q3") + 1
Sheets("COMPLETE LIST").Visible = False
Sheets("FILTERED LIST").Visible = False
Sheets("NEW REVISION").Select |
Partager