GBKEE

¦^´_ 5# sunnyso
§Aªºµ{¦¡§ï¥Î¦Û°Ê¿z¿ï ¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Private Sub Worksheet_Activate()
   
3.     Dim Sh As Worksheet, E As Integer
   
4.     Application.ScreenUpdating = False
   
5.     Application.EnableEvents = False
   
6.     AutoFilterMode = False
   
7.     E = Cells(Rows.Count, "A").End(xlUp).Row
   
8.     E = IIF(E = 5, 6, E)
   
9.     Range("A6:E" & E).Clear
   
10.     For Each Sh In Sheets(Array("DataSheet2", "DataSheet3", "DataSheet3"))
    
11.         Sh.UsedRange.Offset(1).Copy Cells(Cells(Rows.Count, "A").End(xlUp).Row + 1, "A")            '¦X¨Ö¸ê®Æ
    
12.     Next
    
13.     E = Cells(Rows.Count, "A").End(xlUp).Row
    
14.     Range("A5:E" & E).AutoFilter                '½d³ò³]¥ß,¦Û°Ê¿z¿ï.
    
15.     For E = 1 To 5
    
16.         Range("A5").AutoFilter E, , , , False   '¦Û°Ê¿z¿ï: ¨ú®ø½bÀY
    
17.     Next
    
18.     Application.EnableEvents = True
    
19.     Application.ScreenUpdating = True
    
20. End Sub
    
21. Private Sub Worksheet_Change(ByVal Target As Range)
    
22.     Dim M As Variant
    
23.     Application.EnableEvents = False
    
24.     If Target.Row = 4 And Target.Column <= 5 Then
    
25.         M = Split(Target, "#")
    
26.         If UBound(M) >= 1 Then   '
    
27.            '¦Û°Ê¿z¿ï: ¦bÃöÁä¦rµü«e¥[¤J¡i#¡j±N¥HOR¨Ó¿z¿ï¸ÓÄæ¡C
    
28.             Range("A5").AutoFilter Target.Column, "=*" & M(0) & "*", xlOr, "=*" & M(1) & "*"
    
29.         Else
    
30.             Range("A5").AutoFilter Target.Column, "=*" & Target & "*"
    
31.         End If
    
32.     End If
    
33.     Application.EnableEvents = True
    
34. End Sub
    
35. Private Sub Worksheet_SelectionChange(ByVal Target As Range)  '²Ä4¦C¿é¤J«áªð¦^²Ä4¦C
    
36.     Application.EnableEvents = False
    
37.     If Target.Row = 5 And Target.Column <= 5 Then
    
38.         Selection.Offset(-1).Select         'Target.Offset(-1).Select
    
39.     End If
    
40.     Application.EnableEvents = True
    
41. End Sub

½Æ»s¥N½X