[µo°Ý] ¦p¦ó§Q¥ÎSheet1ªºQÄæÃC¦â·í¼Ð·Ç¡A½Æ»s·í¦C¤§C:Eªº­È¦ÜSheet2

´Ñ»y³¾»ï

½Ð°Ý¦p¦ó½Æ»s·íSheet1¤¤ªºQÄ欰µµ¦â®É¡A±N¨ä·í¦C£xcÄæ¨ìEÄ檺­È½Æ»s¦ÜSheet2ªºA13:C13¡A²Ä¤G­Óªº­È¶K¦ÜA14:C14¥H¦¹Ãþ±À¡A±N·í¦CQÄ榳µµ¦â³¡¤À£xC:E¶K§¹¡A°Ñ¦ÒÀɮצbªþ¥ó¡A½Ð°Ý¦¹Ãþªº¥¨¶°¸Ó¦p¦ó¼g?? TEST.rar (12.06 KB)

¤U¸ü¦¸¼Æ: 4

2011-6-1 20:23

mark15jill

¦^´_ 1# ´Ñ»y³¾»ï
­Y¬O­n¥H ¦³µµ¦â Àx¦s®æªº¦C¤U¥h°µ½Æ»sªº¸Ü
    ¥i¥H¥Î¿z¿ï ¥ý±Nµµ¦â³¡¤À°Ï§O¶}¨Ó
    §@ªk
     1.µµ¦â¨ºÄæ¦ì§Ë­Ó¼ÐÃD ¦A±q BÄæ¦ì ¨ìQÄæ¦ì ¤Ï¥Õ«á «ö¿z¿ï
     2.¦b¦³µµ¦âªº¨ºÄæ¦ì ¿ï¾Ü ¨Ì¦â±m¿z¿ï µM«á¿ïµµ¦â
     3.µ¥¨ì¥þ³¡¿z¿ï¥X¨Ó ¦b½Æ»s ¿ï¾Ü­n¶K¤Wªº¬¡­¶Ã¯ ¶K¤W§Y¥i

ªþÀɪº¥N½X

1.     Sheets("sheet1").Select   '---- ¤@¶}©l­n½Æ»sªº¬¡­¶Ã¯¦WºÙ  ¦¹¦æ¤£¥[ªº¸Ü  ¦³¥i¯à¿ù¶Ã
   
2.     Columns("B:Q").Select   '--------   ¸ê®Æ½d³ò (¥]§t ÃC¦âªºQÄæ¦ì)
   
3.     Selection.AutoFilter
   
4.     ActiveSheet.Range("$B$2:$Q$50").AutoFilter Field:=16, Criteria1:=RGB(112, _
   
5.         48, 160), Operator:=xlFilterCellColor
   
6.     Range("B3:G32").Select
   
7.     Selection.Copy
   
8.     Sheets("Sheet4").Select  '-----  ­n¶K¤Wªº¬¡­¶Ã¯¦WºÙ
   
9.     Range("G13").Select    '---- ­n¶K¤Wªº³Ì¥ª¤W¨¤Àx¦s®æ¦ì¸m
   
10.     ActiveSheet.Paste
    
11.     ActiveWindow.SmallScroll Down:=6
    
12.     Sheets("Sheet1").Select
    
13.     Range("F4").Select
    
14.     Application.CutCopyMode = False
    
15.     Selection.AutoFilter

½Æ»s¥N½X

ªþÀɤº ¤w¸g¦³±N¨ä§Ë¦¨¥¨¶° ½Ð¸ÕÅç¬Ý¬Ý§a..

TEST-ÀÉ®×.rar (14.17 KB)

¤U¸ü¦¸¼Æ: 10

2011-6-2 11:10

´Ñ»y³¾»ï

¦^´_ 2# mark15jill


    ¸ÕÅçµ²ªG¥i¥H!½Ð°Ý§Ú­n¦p¦ó±N¦¹¥¨¶°§ï¬°!¤@¶}±ÒSheet2´N¦Û°Ê°õ¦æ!
¦L¶H¤¤¦n¹³¬O§â¶}ÀY§ï¦¨³o­Ó§Y¥i==>Private Sub Worksheet_Activate()
¦ý¬O¤£ª¾¹D¬°¤°»ò·|¥X²{¿ù»~??
ÁÙ¬O§Ú¥´¿ù¤F??

mark15jill

¦^´_ 3# ´Ñ»y³¾»ï

¤w¸g§ËOK¤F...
¦ý¬O±j¯P«Øij ¤£­n¾Õ§ïµ{¦¡½X...¤£µM  ¼K¼K¼K¼K¼K¼K(·|·í±¼³á..
³o¤£¬O§Ú³]¤Uªº³´¨À ¦Ó¬O ¾Õ§ïªº¸Ü ·|¶i¤J¦º¦^°é.. ¤S+¤W§Ú¬O¼g¦¨ ¬¡­¶Ã¯¬¡°Êªºª¬ºA ©Ò¥H..

PS ³o­ÓÀÉ®× ¬O¥i¥Hª½±µ¥HÄæ¦ì ¤U¥h°µ¿z¿ï ¦Ó¨S¦³«ü©w¯S©wªºÀx¦s®æ...
¦pªG­n·s¼W ½Ð±N Äæ¦ìÂX¥R§Y¥i...
¦p A:P  ->aÄæ¦ì¨ì PÄæ¦ì


¦³¨âºØ¤èªk¥i¥H¸ÕÅç
1.«ö¶s command
2.®Ö¿ïcheck

¥N½X¦p¤U

1. '------¦bsheet1¤U
   
2. Private Sub CheckBox1_Change()
   
3. If CheckBox1.Value = True Then
   
4.     Columns("A:P").Select
   
5.     Selection.AutoFilter
   
6.     Range("Q1").Select
   
7.     ActiveSheet.Range("$A$1:$P$49").AutoFilter Field:=16, Criteria1:=RGB(112, _
   
8.         48, 160), Operator:=xlFilterCellColor
   
9.     Columns("A:P").Select
   
10.     Range("P1").Activate
    
11.     Selection.Copy
    
12.     Sheets("Sheet4").Select
    
13.     ActiveSheet.Paste
    
14.     Sheets("Sheet1").Select
    
15.     Selection.AutoFilter
    
16.     Sheets("Sheet1").Select
    
17.    
    
18. End If
    
19.         CheckBox1.Value = False
    
20.     Columns("A:P").Select
    
21.     Range("P1").Activate
    
22.     Selection.AutoFilter
    
23. 
    
24. End Sub
    
25. 
    
26. 
    
27. Private Sub CommandButton1_Click()
    
28.     Columns("a:p").Select
    
29.     Range("p1").Activate
    
30.     Selection.AutoFilter
    
31.     Range("q1").Select
    
32.     ActiveSheet.Range("$B$1:$Q$50").AutoFilter Field:=16, Criteria1:=RGB(112, _
    
33.         48, 160), Operator:=xlFilterCellColor
    
34.     Columns("a:d").Select
    
35.     Selection.Copy
    
36.     Sheets("Sheet4").Select
    
37.    
    
38.     ActiveSheet.Paste
    
39.     Sheets("Sheet1").Select
    
40.     Columns("p:p").Select
    
41.     Application.CutCopyMode = False
    
42.     Selection.Copy
    
43.     Sheets("Sheet4").Select
    
44. 
    
45.     Application.CutCopyMode = False
    
46. Sheets("sheet1").Select
    
47.     Columns("a:p").Select
    
48.     Range("p1").Activate
    
49.     Selection.AutoFilter
    
50. End Sub

½Æ»s¥N½X

1. '-sheet4¤U
   
2. 
   
3. Private Sub Worksheet_Activate()
   
4. If Range("a1").Value = "½s¸¹" Then
   
5.         Columns("F:O").Select
   
6.     Selection.ClearContents
   
7.     Selection.Delete Shift:=xlToLeft
   
8.     Range("A1").Select
   
9.     End
   
10. End If
    
11. 
    
12. End Sub

½Æ»s¥N½X

TEST-ÀÉ®×01.rar (210.62 KB)

¤U¸ü¦¸¼Æ: 4

2011-6-3 10:30

Hsieh

¦^´_ 3# ´Ñ»y³¾»ï

1. Private Sub Worksheet_Activate()
   
2. [A:C] = ""
   
3.    With Sheets("sheet1")
   
4.     .Columns("B:Q").AutoFilter
   
5.     .Range("$B$2").CurrentRegion.AutoFilter Field:=16, Criteria1:=RGB(112, _
   
6.         48, 160), Operator:=xlFilterCellColor
   
7.    .[C:E].SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeVisible).Copy [A12]
   
8.    .Columns("B:Q").AutoFilter
   
9.    End With
   
10. End Sub

½Æ»s¥N½X

´Ñ»y³¾»ï

¦^´_ 5# Hsieh
½Ð°Ý²Ä¤G¦æA:C¬O¤°»ò·N«ä??
¦pªG§Ú¤£­n½Æ»s¨ì²Ä¤G¦CªºA   B  C
·Qª½±µ±qC3¶}©l½Æ»s¡A­n¦p¦ó­×§ï(·N«ä´N¬O¤£­n½Æ»sC2:E2)???

Hsieh

1. Private Sub Worksheet_Activate()
   
2. [A12:C1048576] = "" '²MªÅ¥ý«e¸ê®Æ
   
3.    With Sheets("sheet1")
   
4.     .Columns("B:Q").AutoFilter '¦Û°Ê¿z¿ï
   
5.     .Range("$B$2").CurrentRegion.AutoFilter Field:=16, Criteria1:=RGB(112, _
   
6.         48, 160), Operator:=xlFilterCellColor '¿z¿ïÃC¦â
   
7.    .Range(.[C3], .[C1048576].End(xlUp).Offset(, 2)).SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeVisible).Copy [A13] '½Æ»s¥i¨£Àx¦s®æ¨ìA13
   
8.    .Columns("B:Q").AutoFilter '¨ú®ø¦Û°Ê¿z¿ï
   
9.    End With
   
10. End Sub

½Æ»s¥N½X

´Ñ»y³¾»ï

¦^´_ 8# Hsieh
A12:C1048576,1048576¤£ª¾¬O¤£¬O¤Ó¦h¡A·|¥X²{¿ù»~!§Ú±N¥¦§ï¦¨C100¤§«á§Y¥i¥Î
¦ý¬O:    .Range("$B$2").CurrentRegion.AutoFilter Field:=16, Criteria1:=RGB(112, _
        48, 160), Operator:=xlFilterCellColor '¿z¿ïÃC¦â<==³o¥y¤£ª¾­þ¸Ì¥X¤F°ÝÃD!¤@ª½¥X²{°»¿ù(§ï¤£¦n)??

Hsieh

¦^´_ 9# ´Ñ»y³¾»ï
2007ª©¥»³Ì¤j¦C¦ì¬O1048576¨S¿ù°Ú¡A¨º§ï¦¨65536¸Õ¸Õ
¦pªG¤£¬O2007¥H«áª©¥»
±µ¤U¥h¿z¿ï´N¤£·|¦¨¥ß¡A¦]¬°2003ª©¥»µLªk¥HÃC¦â¿z¿ï
­Yª©¥»¥¿½T½Ð±NQÄæ¥[¤WÄæ¦WºÙ
°ò¥»¤W«ØijEXCEL¤£­n¥ÎÃC¦â¨Ó²Î­p¼Æ­È
§AªºQÄæÃC¦â¬O¥Î®æ¦¡¤Æ±ø¥ó¦Ó¨Ó
À³¸Ó¥Î±ø¥ó¦¡¨Ó§PÂ_¦¨¥ß»P§_

´Ñ»y³¾»ï

¦^´_ 10# Hsieh


    §Ú¬O2007ª©ªº!¤£¹L1048576´N¬O¤£¯à??¤£ª¾¬O§Ú¤£¬O§Ú¬O¥Î¤G¶i¦ì¬¡­¶Ã¯ªºÃö«Y!
½Ð°Ý¤@¤U¬°¤°»ò§Ú±N³o¥yªº.Range(.[C3], .[C1048576].End(xlUp).Offset(, 2)).SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeVisible).Copy [A13]
Range(.[C3], .[C1048576]§ï¦¨Range(.[C3], .[C47]
¬°¤°»ò¤£¯à¨ì¹w´Áªº®ÄªG¡A¥L¥u½Æ»sC2:E2¦Ó¤w¡A¦ý¬O±NC47§ï¦¨C100«o¯à¨Ï¥Î??¦]¬°¦bÀɮפ¤C48¦³¤å¦r±Ô­z¡A©Ò¥H¦pªG¥ÎC100·|±N¤å¦r§R±¼!¡A©Ò¥H¯à§_²²¤¿z¿ïªº½d³ò??