[µ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

§Ú¦Û¤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

idsmchow

¦^´_ 8# Hsieh


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

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

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

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¦¸ÁÂÁÂ

Hsieh

¦^´_ 1# idsmchow

1. Private Sub CommandButton1_Click()
   
2. Dim Sh As Worksheet, Rng As Range
   
3. For Each Sh In Sheets
   
4. If Sh.Name <> "Statement" Then   '²¤¹LStatement¤u§@ªí
   
5. With Sh
   
6.   If .AutoFilter.Filters(3).On Then .ShowAllData   '¦pªGCÄæ¨S¦³³Q¿z¿ï
   
7.      .Range("A1").CurrentRegion.AutoFilter 3, Sheets("Statement").Range("G2"), xlAnd   '´N¥HCÄæ¿z¿ï
   
8.      Set Rng = Intersect(.[A2:D65536], .AutoFilter.Range.SpecialCells(xlCellTypeVisible))    'A:DÄõ¸¦2¦C¥H¤U»P¿z¿ïµ²ªGªº¥i¨£Àx¦s®æ¥æ¶°³¡¤À
   
9.      If Not Rng Is Nothing Then Rng.Copy Sheets("Statement").Range("A65536").End(xlUp).Offset(1, 0)  '½Æ»s
   
10. End With
    
11. End If
    
12. Next
    
13. End Su

½Æ»s¥N½X

register313

¦^´_ 6# GBKEE
   
·PÁÂGBKEEª©¤j¤@ª½µ¹¤©«ü¾É

GBKEE

¦^´_ 5# register313
¨S¿ù.¬O¤@¼Ëªº®ÄªG,®t§O¬O§A¦h½Æ»sªÅ¥Õªº¤@¦C.
¦pªG¸ê®Æ®wªº¸ê®Æ¬O³sÄò¨ì¤u§@ªíªº³Ì«á¤@¦C,§A§Úªºµ{¦¡³£·|¥X¿ùªº

register313

¦^´_ 4# idsmchow


µ{¦¡¬O¨Ì¼Ó¥D¤§·N§@³Ì¤Ö¤§­×¥¿
¤]³\¤£§¹¬ü
¦ý¦p¦ó¤£¥¿½T ½Ð»¡©ú²M·¡ Åý§Úª¾¹D°ÝÃD
»PGBKEEª©¤jµ{¦¡°õ¦æ¤§µ²ªG¬O¬Û¦Pªº
Statement.rar (151.54 KB)

¤U¸ü¦¸¼Æ: 7

2011-12-28 00:32

idsmchow

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

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

GBKEE

¦^´_ 1# idsmchow

1. Private Sub CommandButton1_Click()
   
2.     Dim eachsht As Worksheet, eachrng As Range
   
3.     For Each eachsht In Worksheets
   
4.         With eachsht
   
5.             If .Name <> "Statement" Then
   
6.                 .Range("a1").AutoFilter Field:=3, Criteria1:=Sheets("Statement").Range("G2"), Operator:=xlAnd
   
7.                 If .Range("a" & Rows.Count).End(xlUp).Row > 1 Then '¦³¿z¿ï¨ì©Ò»Ýªº¸ê®Æ
   
8.                     Set eachrng = Sheets("Statement").Range("a" & Rows.Count).End(xlUp).Offset(1)
   
9.                     .Rows("2:" & .Range("a" & Rows.Count).End(xlUp).Row).Copy eachrng
   
10.                 End If
    
11.                 .Range("a2").CurrentRegion.AutoFilter              '¨S¦³«ü©w·Ç«h->Åã¥Ü¥þ³¡¸ê®Æ
    
12.             End If
    
13.         End With
    
14.     Next
    
15. End Sub

½Æ»s¥N½X