ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

½Ð°Ý¦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)

¥»©«³Ì«á¥Ñ mark15jill ©ó 2011-6-2 11:14 ½s¿è

¦^´_ 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)

TOP

¦^´_ 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??

TOP

¥»©«³Ì«á¥Ñ mark15jill ©ó 2011-6-3 10:36 ½s¿è

¦^´_ 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. End Sub


  24. Private Sub CommandButton1_Click()
  25.     Columns("a:p").Select
  26.     Range("p1").Activate
  27.     Selection.AutoFilter
  28.     Range("q1").Select
  29.     ActiveSheet.Range("$B$1:$Q$50").AutoFilter Field:=16, Criteria1:=RGB(112, _
  30.         48, 160), Operator:=xlFilterCellColor
  31.     Columns("a:d").Select
  32.     Selection.Copy
  33.     Sheets("Sheet4").Select
  34.    
  35.     ActiveSheet.Paste
  36.     Sheets("Sheet1").Select
  37.     Columns("p:p").Select
  38.     Application.CutCopyMode = False
  39.     Selection.Copy
  40.     Sheets("Sheet4").Select

  41.     Application.CutCopyMode = False
  42. Sheets("sheet1").Select
  43.     Columns("a:p").Select
  44.     Range("p1").Activate
  45.     Selection.AutoFilter
  46. End Sub
½Æ»s¥N½X
  1. '-sheet4¤U

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

  10. End Sub
½Æ»s¥N½X
TEST-ÀÉ®×01.rar (210.62 KB)

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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)???

TOP

  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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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)??

TOP

¦^´_ 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§_
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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³ò??

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD