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

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

¥»©«³Ì«á¥Ñ 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

¥»©«³Ì«á¥Ñ 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

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD