[µo°Ý] ¿z¿ï¸ê®Æ°ÝÃD?

idsmchow

½Ð°Ý¦p¦ó­×§ï¥H¤Uªº¥¨¶°??

Private Sub CommandButton1_Click()
Dim eachsht As Worksheet, eachrng As Range, tmpTbl As Range
For Each eachsht In Worksheets
If eachsht.Name <> "Statement" Then
Set eachrng = Sheets("Statement").Range("a65536").End(xlUp).Offset(1)
Set tmpTbl = eachsht.Range("a2").CurrentRegion
eachsht.Range("a2").CurrentRegion.AutoFilter Field:=3, Criteria1:=Sheets("Statement").Range("G2"), Operator:=xlAnd
tmpTbl.Rows("2:" & tmpTbl.Rows.Count).Copy eachrng
End If
Next
End Sub

·í¥G¦X±ø¥óªº­n¨D, ¨Ò¦p "AA Ltd" ±ø¥ó³£¦b Jan , Feb. Mar Sheet - ¸ê®Æªí¥X²{ªº¸Ü, ³o¥¨¶°´N¯à¥¿½T¿z¿ï©Ò»Ýªº¸ê®Æ¤Î½Æ»s¨ìStatement Sheet.

¦ý±ø¥ó¤£¥G¦X¸ê®Æªíªº¸Ü ¨Ò¦p "EE Ltd" ³o¥¨¶°´N·|±N©Ò¦³¨S¦³Ãö«Yªº¸ê®Æ¤@¨ÖÂà¼g.

°ÝÃD¬O§_¥X©ó ' tmpTbl.Rows("2:" & tmpTbl.Rows.Count)' ³o¤@¥y¤W?

½Ð°Ý¦p¦ó­×¥¿?

ÁÂÁÂ

idsmchow

°ÝÃD¤w¸Ñ¨M, ÁÂÁ¦U¦ìªº¦^ÂÐ

¦ýregister313 ªºµª®×
tmpTbl.Rows("2:" & tmpTbl.Rows.Count +1)
¦n¹³¤£¥¿½T

idsmchow

¦^´_ 5# register313


    Hi register313,

§Aªº¸Ñµª¬O¥¿½T, ¥u¬O¦Û¤v§ï°Ê¤F¤@¨Ç®æ¦¡, ¦Ó¾É­P¥X²{°ÝÃD.
¥»¤H¬Oªì¾ÇªÌ, ­è­è¶R¤F´X¥»®Ñ¦Û¾Ç, ¤é«á½Ð¦h¦h«ü±Ð.

¹ï©ó¤é«eªº»¡ªk, ½Ð¦h¦h¥]²[

¦A¦¸ÁÂÁÂ

idsmchow

¦^´_ 8# Hsieh


    ¶W¯Åª©¥D¡@©Ò¼gªº¥¨¶°¦³³¡¥÷»yªk, ¦b§ÚÁʶRªº®Ñ¥»¤¤¨S¦³´£¤Î¹L¡C¨Ò¦pOn Then.¡@Intersect.¡@¤ñ¸û°ª²`

¹ï©ó§Ú¡]ªì¾ÇªÌ¡^¨Ó»¡¡A¡@»Ý­n®É¶¡²z¸Ñ¡C

ÁÂÁ¤j®a¦^ÂСC

idsmchow

§Ú¦Û¤v¤]±q®Ñ¥»¤¤¡A±N¤@¨Ç¥¨¶°­×§ï¡A¼g¤F¤@­Ó¥¨¶°¡A¤£ª¾¬O§_¥¿½T¡C
§Æ±æ¤j®a­×§ï¡Aµ¹¤@¨Ç·N¨£

Sub Click()
Dim eachsht As Worksheet, eachrng As Range, tmpTbl As Range
Dim myFld As Integer, I As Integer, Q As Range

For Each eachsht In Worksheets
If eachsht.Name <> "Statement" Then

Set eachrng = Sheets("Statement").Range("a65536").End(xlUp).Offset(1)
Set tmpTbl = eachsht.Range("a2").CurrentRegion
Set Q = Sheets("Statement").Range("G2")
myFld = 3

For I = 2 To 180
If tmpTbl.Cells(I, myFld).Value = Q Then
eachsht.Range("a2").CurrentRegion.AutoFilter Field:=3, Criteria1:=Q, Operator:=xlAnd
tmpTbl.Rows("2:" & tmpTbl.Rows.Count).Copy eachrng
End If
Next

End If
Next

End Sub