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

[µo°Ý] ½Ð°Ý¦³¿ìªk¥[§ÖVBA°õ¦æªº³t«×¶Ü?

[µo°Ý] ½Ð°Ý¦³¿ìªk¥[§ÖVBA°õ¦æªº³t«×¶Ü?

¥D­n¬O§Q¥ÎVBA ¶i¦æ¸ê®Æ¿z¿ï«á
±N»Ý­nªº¸ê®Æ¥ÑSheet1(¥À¸ê®Æ­¶­±)½Æ»s¨ìSheet2(¤l¸ê®Æ­¶­±
¦ý¥À¸ê®Æ­¶­±¨½¬ù¦³4¸U¦C¡A¨C¦C¬ù¦³40¦æ)
¦]¦¹°õ¦æ°_¨Ó³t«×«ÜºC
ÁٽЦU¦ì°ª¤H«üÂI¡A¬O§_¦³¿ìªk§ïµ½°õ¦æ¼Æ«×
·PÁÂ

VBA code

vb code.gif
2013-1-28 16:14


¥Ñ©óExcel ÀɮפӤj
¦]¦¹¥ý¤W¶Ç µ{¦¡½X..

¦^´_ 1# m077

¸ê®Æ/¿z¿ï
ªº¥\¯à¦³¸Õ¹L¶Ü?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

¦]¬°¥D­n¥Øªº¬O·Q­n±N¤£¦P±ø¥óªº¸ê®Æ¿W¥ß¨ì¤£¦Psheet §@«áÄòªº¤ÀªR
­ì¥ýªº¤è¦¡¬O¤U­±³oºØ(¤£ª¾¹D±z«üªº¬O¤£¬O³oºØ¤è¦¡)
    [attach]14090[/attach]

¤£¹Lµo²{·í¸ê®Æ¼Æ¤Ó¤j®É¡A±`·|µo²{¤l¸ê®Æ­¶­±¸Ìªº¸ê®Æ·|¦³ºUÂø¨ä¥L±ø¥óªº¸ê®Æ(¿z¿ï¤£§¹¾ã)
¦]¦¹¤~¹Á¸Õ³oÁ`¤è¦¡~

TOP

¦^´_ 1# m077
¨S¤W¶Ç ... ¦Û¤v¸Õ¸Õ¦æ§_
  1. Sub Test()
  2. Application.ScreenUpdating = False
  3. Application.EnableEvents = False

  4. Dim r As Long, i As Long
  5. Dim ar, rngCopy As Range

  6. With Workbooks("vbtest.xls")
  7.     With .Sheets("sheet1")
  8.         r = .Cells(.Rows.Count, "G").End(xlUp).Row
  9.         ar = Application.Transpose(.Range(.Cells(1, 30), .Cells(r, 30)).Value)
  10.         
  11.         For i = 2 To r
  12.             If ar(i) = "C2A4TST1" Then
  13.                 If rngCopy Is Nothing Then Set rngCopy = .Rows(i) Else Set rngCopy = Union(rngCopy, .Rows(i))
  14.             End If
  15.         Next
  16.         
  17.         .Select
  18.         rngCopy.Copy .Sheets("sheet2").Range("A" & .Rows.Count).End(xlUp).Offset(1)
  19.         .Sheets("sheet2").Select
  20.     End With
  21. End With

  22. Set rngCopy = Nothing
  23. Application.EnableEvents = True
  24. Application.ScreenUpdating = True
  25. End Sub
½Æ»s¥N½X

TOP

¦^´_  Hsieh

¦]¬°¥D­n¥Øªº¬O·Q­n±N¤£¦P±ø¥óªº¸ê®Æ¿W¥ß¨ì¤£¦Psheet §@«áÄòªº¤ÀªR
­ì¥ýªº¤è¦¡¬O¤U­±³oºØ ...
m077 µoªí©ó 2013-1-28 16:48


¸É¤W¹Ï¤ù
111.JPG
2013-1-30 13:14

TOP

­è¸Õ¤F¤@¤U S¤jªº¤è¦¡¡A³t«×¤è­±¦n¹³®t¤£¦h
¤£¹LÁÙ¬O«Ü·PÁÂS¤j

·Q¤F¤@¤U¡Aµ{¦¡°õ¦æ³t«×ºC¥D­n¬O¦]¬°§â¸ê®Æ¦s¦b¼È¦s°O¾ÐÅ餤
µ¥¨ì°j°é°õ¦æ§¹¦¨«á¡A¤~¤@¦¸¶×¥X
¦pªG¥i¥H§ï¦¨³vµ§¶×¥Xªº¤è¦¡
¬O§_¯à°÷§ïµ½?

¦pªG­n§ï¦¨³vµ§¶×¥X¡A¤£ª¾¹D­n«ç­×§ï¤~¦³¿ìªk?

TOP

ÁÙ¬Oı±o¥Î¶i¶¥¿z¿ï§Ö

TOP

¦^´_ 6# m077
¬Ý¨ì¹Ï¤ù¤F..
³vµ§¶×¥XÀ³¸Ó¬O§óºC

§AÀ³¸Ó§â±ø¥ó¦C¥X¨Ó  ¬Ý¬O¶i¶¥¿z¿ï±ø¥ó­þ¸Ì¥i§ïµ½
¤~¯à¿z¨ì§A­nªºµ²ªG·|¤ñ¸û¦n

TOP

A=Array("sheet1","sheet2",...."sheetn")
B=Array("Criterial1","Criterial2",..."Criterialn")
for each i in A
   sheets(i).select
   ****advancedfilter
next i
lmh

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD