GBKEE

¦^´_ 1# jesscc

1. Sub Ex()
   
2.     Dim Wo As Workbook, Sh As Worksheet, Rng As Range
   
3.     For Each Wo In Workbooks
   
4.         If Wo.Name <> "SPECforMRPtest.xls" Then
   
5.             For Each Sh In Wo.Sheets
   
6.                 With Workbooks("SPECforMRPtest.xls").Sheets("MRP").Cells(Rows.Count, "aw").End(xlUp).Offset(1)
   
7.                     Set Rng = Sh.Range(Sh.[d7], Sh.[d7].End(xlDown))
   
8.                     .Resize(Rng.Count, 1) = Rng.Value
   
9.                     .Offset(, 1).Resize(Rng.Count, 1) = Rng.Offset(, 4).Value
   
10.                 End With
    
11.             Next
    
12.         End If
    
13.     Next
    
14. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 3# jesscc
¥u­n§â Ay2() ùتº¨º­Ó "s" ¥þ³¡´«¦¨¥t¤@­Ó¥N¸¹´N¦n¤F¡C  ³oÆ[©À¿ù»~
Dim Ay2()«Å§i¬°°ÊºA°}¦C
ReDim Preserve Ay2(s) Ä~©Ó«e­±ªº S ·|¦³26­ÓªÅ¥Õ¤¸¯À
Ay2(s) =Array(Cells(j, ar2(0)).Value, Cells(j, ar2(1)).Value)  ¬O¤@ºû°}¦C
©Ò¤@¸ê®Æ«¬ºA¤£²Å¦X
±NS³]¬°0 ¤§«á -> Ay2()¸Ìªº¤¸¯À¸ê®Æ«¬ºA¤~·|¤@­P

1. '¨ú¥X Metal ¥X³f©ú²Ó
   
2. aw.Activate
   
3. Sheets("Metal").Activate
   
4. s = 0  '    ½Ð¦bµ{¦¡½X¥[¤W³o¦æ     
   
5. Dim Ay2()
   
6. ar2 = Array("D", "H")
   
7. For j = 7 To Cells(Rows.Count, 2).End(3).Row
   
8.     ReDim Preserve Ay2(s)
   
9.     Ay2(s) = Array(Cells(j, ar2(0)).Value, Cells(j, ar2(1)).Value)
   
10.     s = s + 1
    
11. Next
    
12. Workbooks("SPECforMRPtest.xls").Activate
    
13. With Sheets("MRP")
    
14.    .Cells(.Rows.Count, 49).End(xlUp).Offset(1, 0).Resize(s, 2) = Application.Transpose(Application.Transpose(Ay2))
    
15. End With   
    
16. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 5# jesscc
´«¤@­Ó¥N¸¹©M´¡¤Js = 0 ³o¨âªÌ¤S¦³¤°»ò®t§O©O?·Pı¦n¹³³£¦³Âk¹sªº·N«ä¹À   ¨S¦³®t§O¦ý´N¬O­nÂk¹s.

'¨ú¥X Etrusion ¥X³f©ú²Ó
Sheets("Etrusion").Activate
Dim Ay1()   
''
         ReDim Preserve Ay1(s)
       Ay1(s) = Array(Cells(i, ar1(0)).Value, Cells(i, ar1(1)).Value)
          s = s + 1
      Next
'
'¨ú¥X Metal ¥X³f©ú²Ó
µ{¦¡¶i¦æ¨ì¦¹  s=26
Dim Ay2()   
ar2 = Array("D", "H")
For j = 7 To Cells(Rows.Count, 2).End(3).Row         
       ReDim Preserve Ay2(s)                                      '  ·í j=7 ®É Ay°t¸m26­ÓµL¸ê®ÆªºÀx¦sªÅ¶¡
       Ay2(s) = Array(Cells(j, ar2(0)).Value, Cells(j, ar2(1)).Value)  'Ayªº²Ä26Àx¦sªÅ¶¡¸ê®Æ¬°¤@ºû°}¦C  ........(S=S+1)      
       s = s + 1
      Next
    Workbooks("SPECforMRPtest.xls").Activate
        With Sheets("MRP")
        .Cells(.Rows.Count, 49).End(xlUp).Offset(1, 0).Resize(s, 2) = Application.Transpose(Application.Transpose(Ay2))
'¦]¦¹Ay1()©MAy2()ªº¸ê®Æ§ÎºA´N·|¤£¦P¡A¬O³o­Ó·N«ä¶Ü?   ¤£¬Oªº
'³Ì²×¤F Ay2¾ã­Ó°}¦C¸Ì°ÑÂøªÅ¥Õ¤¸¯À¤Î¤@ºû°}¦C ¸ê®Æªº«¬ºA¤£¤@­P µLªk©ñ¦b  .Cells(.Rows.Count, 49).End(xlUp).Offset(1, 0).Resize(s, 2)
    End With