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

[µo°Ý] ¶K¤J­ÈVBAºÃ°Ý

[µo°Ý] ¶K¤J­ÈVBAºÃ°Ý

VBA CODEªº­ì©l¨Ó·½ : https://alice57.pixnet.net/blog/post/23273559

¤j®a¦n, §Ú¦bºô¸ô§ä¨ì­Ó¶K¤J­ÈªºVBA, ­×§ï«á, ¦³´X­Ó¦a¤è¤£©ú¥Õ, ¦A½Ð±Ð¤@¤U¦U¦ì

1. µµ¦â¦r¸Ìªº"0 + 16"  ³o­Ó¬O¤°»ò·N«ä©O?
2. ¦bCOPY¨ìdSht , ­ì©lªºCODE¬O".Range("B2:B" & y).Copy MySht.[C2]", ¦ý¶K¨ìCÄæ®É, ¸ÓÄæ¦ìªº®æ½u³£·|³Q§R±¼,  ³o­Ó­n«ç»ò§ï©O?
* §Ú¬O§ï¥Î
dSht.Range("B2:B" & y).Copy
MySht.Range("C2:J1000").PasteSpecial Paste:=xlPasteValues
¦³¦¨¥\, ¥u¬O·Qª¾¹D¦b­ì©lªº.Range("B2:B" & y).Copy MySht.[C2]       <---- ¦b³o­ÓCODE¤U­n«ç»ò¼g, ¥u¶K¤J­È´N¦n, ¤£­n°Ê¨ì®æ¦¡

·PÁ¦U¦ì!


Sub ¶KÂd¸¹()
Dim MySht As Worksheet, dSht As Worksheet, y&
  Set MySht = Sheets("¥X³f«á")
  Set dSht = Sheets("¥X³f©ú²Ó")
  With dSht
  MySht.Range("C2:C10000").Clear   '²M°£­ì¦³¸ê®Æ
  dSht.AutoFilterMode = False    '¸Ñ°£¿z¿ï¼Ò¦¡
  y = dSht.Range("B65536").End(xlUp).Row
  If y < 2 Then MsgBox "¡°¨Ó·½ªí¡e¢ÐÄæ¡fµL¸ê®Æ¡I¡@", 0 + 16: Exit Sub
  '¡ô¥ý¸Ñ°£¿z¿ï¡A¦AÀË´ú¸ê®Æ§À¦C¦C¸¹¡A­Y¿z¿ï¤¤¡AÀË´ú¦C¸¹·|¦³»~®t
  If WorksheetFunction.CountIf(dSht.Range("A2:A" & y), "1") = 0 Then
      MsgBox "¡°¨Ó·½ªí¡eAÄæ¡fµL¡e1¡f¿z¿ï­È¡I¡@", 0 + 16: Exit Sub
  End If

  '¡ôÀË´ú¡e1¡f¼Æ
  'ª`·N¡G"~*"¤~¯à³æ¿ï¡e*¡f¡A§_«h¨ä¥¦¤å¦r¡]¨Ò¦p¡GA.B.C¡^¤]·|¿z¿ï¶i¨Ó

      dSht.Range("$A$1:$AB$1500").AutoFilter Field:=1, Criteria1:="1"
      dSht.Range("B2:B" & y).Copy
      MySht.Range("C2:J1000").PasteSpecial Paste:=xlPasteValues
     '­ì©lªºCODE¬O".Range("B2:B" & y).Copy MySht.[C2]", ¦ý¶K¨ìCÄæ®É, ¸ÓÄæ¦ìªº®æ½u³£·|³Q§R±¼

      dSht.AutoFilterMode = False '¸Ñ°£¿z¿ï¼Ò¦¡
      MySht.AutoFilterMode = False '¸Ñ°£¿z¿ï¼Ò¦¡
  End With

¶K¤J­ÈVBA.rar (26.9 KB)

        À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