diabo

1.    '°ÝÃD1
   
2.     end_row = Sheets("ASD1").[A65536].End(xlUp).Row
   
3.     For Each aaa In Array("DL", "BA", "STOCK")
   
4.         With Sheets("ASD1")
   
5.             .Range("A1:C" & end_row).AutoFilter Field:=2, Criteria1:="=" & aaa
   
6.             .Range("A1:C" & end_row).SpecialCells(xlCellTypeVisible).Copy
   
7.              Sheets(aaa).Range("A1").PasteSpecial Paste:=xlPasteValues
   
8.         End With
   
9.     Next

½Æ»s¥N½X

diabo

°O±o¦b VBE¤¤³]©w¤Þ¥Î¶µ¥Ø¤Ä¿ï Microsoft ActiveX Data Objects 2.x Library

1.    '°ÝÃD2
   
2.     Dim strConn As String, strSQL As String
   
3.     Dim conn As ADODB.Connection
   
4.     Dim rs As ADODB.Recordset
   
5.    
   
6.     strConn = "Driver={Microsoft Excel Driver (*.xls)};" & _
   
7.               "DBQ=" + ThisWorkbook.FullName + ";" & _
   
8.               "ReadOnly=True"
   
9.       
   
10.     Set conn = CreateObject("ADODB.Connection")
    
11.     Set rs = CreateObject("ADODB.Recordset")
    
12.    
    
13.     strSQL = "SELECT ITNO, SUM(TQT) AS TQT2 From ((SELECT ITNO,TQT FROM [ASD1$] WHERE LEN(WHS)=6 ORDER BY ITNO) tmpTable) GROUP BY ITNO"
    
14.    
    
15.     conn.Open strConn
    
16.     rs.Open strSQL, conn, 3, 1
    
17.    
    
18.     With Sheets("BFTTL")
    
19.         .Range("A2:B" & .[A65536].End(xlUp).Row).Cells.ClearContents
    
20.         .Cells(2, 1).CopyFromRecordset rs
    
21.     End With
    
22.         
    
23.     rs.Close
    
24.     Set rs = Nothing
    
25.    
    
26.     conn.Close
    
27.     Set conn = Nothing

½Æ»s¥N½X

diabo

¬Ý¤£À´°ÝÃD3