GBKEE

¦^´_ 2# ounmaxz

1. Sub Ex()
   
2.     Dim Rng As Range, CopyRng As Range, I As Integer
   
3.     Set Rng = Workbooks("¤u®É¾ã²z.xlsm").Sheets(1).[a1]
   
4.     For I = 1 To 16
   
5.         With Workbooks.Open("Q:\M" & I & ".xlsx")
   
6.             Set CopyRng = .Sheets(1).UsedRange.Offset(3)
   
7.             Rng.Resize(CopyRng.Rows.Count, CopyRng.Columns.Count).Value = CopyRng.Value
   
8.             Set Rng = Rng.Offset(IIf(I = 1, 999, 1000))
   
9.             .Close False
   
10.         End With
    
11.     Next
    
12. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 4# lilytracy
¼Ó¥D»¡ªº:
¤p§Ì¥Ø«e»Ý¾ã²z«Ü¦h¤H¤u®É°ÝÃD§Æ±æ±q[¤u®É¾ã²z]Àɤº¶×¤J M1~M16 ªºÀɮפº®e±Æ§Ç¤è¦¡¬°

GBKEE

¦^´_ 6# davidoff
µ{¦¡½X §ï¼g¦b¤@¯ë¼Ò²Õ ©ÎThisWorkbook¼Ò²Õ
§Aªºµ{¦¡½X ¬O¼g¦b ¤u§@ªíª«¥ó¼Ò²Õ   
Range ¨S«ü©ú¤u§@ªí ´N¬O±MÄÝ©ó³o¤u§@ªíª«¥ó¼Ò²Õªº¤u§@ªíRange
§A¤£¯à¦b¨ä¥L¤u§@ªí¤¤ °õ¦æ³o ±MÄÝ©ó³o¤u§@ªíª«¥ó¼Ò²Õªº¤u§@ªíRange

GBKEE

¦^´_ 8# ounmaxz

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng As Range, NameRng As Range, CopyRng As Range, E As Range
   
4.     Set NameRng = Workbooks("¤u®É¾ã²z.xlsm").Sheets(2).[A1:A16]    '¤H¦Wªº½d³ò ©Î¬O §¹¾ãªº¸ô®|ÀɮצWºÙ
   
5.     Set Rng = Workbooks("¤u®É¾ã²z.xlsm").Sheets(1).[a1]
   
6.     For Each E In NameRng
   
7.         With Workbooks.Open("Q:\" & E & ".xlsx")  'E=¤H¦W
   
8.         'With Workbooks.Open(E)   '¦p E = §¹¾ãªº¸ô®|ÀɮצWºÙ
   
9.             Set CopyRng = .Sheets(1).UsedRange.Offset(3)
   
10.             Rng.Resize(CopyRng.Rows.Count, CopyRng.Columns.Count).Value = CopyRng.Value
    
11.             Set Rng = Rng.Offset(IIf(Rng.Row = 1, 999, 1000))
    
12.             .Close False
    
13.         End With
    
14.     Next
    
15. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 10# ounmaxz

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng As Range, NameRng As Range, CopyRng As Range, E As Range
   
4.     Set NameRng = Workbooks("¤u®É¾ã²z.xlsm").Sheets(2).[A1:A16]    '¤H¦Wªº½d³ò ©Î¬O §¹¾ãªº¸ô®|ÀɮצWºÙ
   
5.     Set Rng = Workbooks("¤u®É¾ã²z.xlsm").Sheets(1).[a1]
   
6.     For Each E In NameRng
   
7.         With Workbooks.Open("Q:\" & E & ".xlsx")  'E=¤H¦W
   
8.         'With Workbooks.Open(E)   '¦p E = §¹¾ãªº¸ô®|ÀɮצWºÙ
   
9.             Set CopyRng = .Sheets(1).UsedRange.Offset(3)
   
10.             Rng.Resize(CopyRng.Rows.Count, CopyRng.Columns.Count).Value = CopyRng.Value
    
11.             If Rng.End(xlDown).Row = Rows.Count Then
    
12.                 Set Rng = Rng.End(xlDown).End(xlUp).Offset(1)
    
13.             Else
    
14.                 Set Rng = Rng.End(xlDown).Offset(1)
    
15.             End If
    
16.             .Close False
    
17.         End With
    
18.     Next
    
19. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 13# ounmaxz
¤W¶Çµ{¦¡½X¬Ý¬Ý

GBKEE

¦^´_ 15# ounmaxz
A1:A16 ¤¤¦p¦³ªÅ¥Õ ·íµM·|¦³¿ù»~
Set NameRng = Workbooks("123.xlsm").Sheets(2).[A1:A16]   
§ï¦¨   A1:A16 ¤¤¦³¸ê®Æªºµ{¦¡½X
Set NameRng = Workbooks("123.xlsm").Sheets(2).[A1:A16].SpecialCells(xlCellTypeConstants)  
A1:A16 ¦p¥þ³¡¬OªÅ¥Õ(¨S¦³¸ê®Æ)¤]¬O¤@¼Ë·|¦³¿ù»~

GBKEE

¦^´_ 18# ounmaxz
µ{¦¡¶}©l®É  °±¤î¿Ã¹õ§ó·s   Application.ScreenUpdating = False
µ{¦¡µ²§ô«e  «ì´_¿Ã¹õ§ó·s  Application.ScreenUpdating = True