ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°ª¤HÀ°¦£°£¿ù¡AÁÂÁÂ~

[µo°Ý] ½Ð°ª¤HÀ°¦£°£¿ù¡AÁÂÁÂ~

01.Option Explicit

02.Sub Ex()

03.   Dim Rng As Range

04.   'With Workbooks.Open("C:\USER\DESTOP\E.XLSX").Sheets("2012") 'ÀÉ®×¥¼¶}±Ò®É¥Î¦¹µ{¦¡½X

05.   With Workbooks("E.XLSX").Sheets("2012")                      'Àɮפw¶}±Ò®É¥Î¦¹µ{¦¡½X

06.        'A2:AM2 to A100:AM100 ¬OY:\2012\A.XLSX (2012) ªº¸ê®Æ

07.        Set Rng = .[A2]

08.        With Workbooks.Open("Y:\2012\A.XLSX").Sheets("2012")    'Àɮ׶}±Ò

09.            .[A100:AM100].Copy Rng   ¡§½Ð°Ý¦pªG§Úªº¸ê®Æ¤£°±¼W¥[¡A¶W¹L100¦C¡A³o¥y¬O¤£¬O»Ý­n§ï¡H

10.           .Parent.Close False                                  'ÀÉ®×Ãö³¬

11.        End With

12.        'A101:AM101 to A150:AM150¬OC:\2012\B.XLSX (Nov)ªº¸ê®Æ

13.        Set Rng = Rng.End(xlDown).Offset(1)  

14.        With Workbooks.Open("Y:\2012\A.XLSX").Sheets("Nov")    'Àɮ׶}±Ò

15.            .[A150:AM150].Copy Rng   ¡§½Ð°Ý¦pªG§Úªº¸ê®Æ¤£°±¼W¥[¡A¶W¹L150¦C¡A³o¥y¬O¤£¬O»Ý­n§ï¡H

16.           .Parent.Close False                                  'ÀÉ®×Ãö³¬

17.        End With

18.        'A151:AM151 to A270:AM270¬OZ:\2012\C.XLSX (2012) ªº¸ê®Æ

19.        Set Rng = Rng.End(xlDown).Offset(1)

20.        With Workbooks.Open("Y:\2012\A.XLSX").Sheets("2012")    'ÀÉ®×¥¼¶}±Ò

21.            .[A270:AM270].Copy Rng   ¡§½Ð°Ý¦pªG§Úªº¸ê®Æ¤£°±¼W¥[¡A¶W¹L270¦C¡A³o¥y¬O¤£¬O»Ý­n§ï¡H

22.           .Parent.Close False                                  'ÀÉ®×Ãö³¬

23.        End With

24.    End With

25.End Sub



Set Rng = Rng.End(xlDown).Offset(1)  ³o¥y¥X²{run-time error '1004' application-defined or object-defined error
.[A100:AM100].Copy Rng   ¡§½Ð°Ý¦pªG§Úªº¸ê®Æ¤£°±¼W¥[¡A¶W¹L100¦C¡A³o¥y¬O¤£¬O»Ý­n§ï¡H
.[A150:AM150].Copy Rng   ¡§½Ð°Ý¦pªG§Úªº¸ê®Æ¤£°±¼W¥[¡A¶W¹L150¦C¡A³o¥y¬O¤£¬O»Ý­n§ï¡H
.[A270:AM270].Copy Rng   ¡§½Ð°Ý¦pªG§Úªº¸ê®Æ¤£°±¼W¥[¡A¶W¹L270¦C¡A³o¥y¬O¤£¬O»Ý­n§ï¡H

¦^´_ 53# GBKEE


    ½Ð°Ý¬°¦ó³o­Ófile·|¦³10MB¡A¥u¦³³o­Óµ{¦¡¡A¨ä¥Lªº³£¨S¦³¡H

TOP

¦^´_ 52# 198188

   


¦p¹Ï¾Þ§@¥i¤è«K¥L¤H½Æ»sµ{¦¡½X
²¤Æ§Aªºµ{¦¡½X
  1. Option Explicit
  2. Sub Ex()
  3.    Dim Rng(1 To 2) As Range, Files_AR(), E As Variant
  4.      Files_AR = Array("Connie.XLSX", "Lily.XLSX", "Jane.XLSX", "Jenny.XLSX")
  5.                                                                        'ÀɮצWºÙ¸m¤J°}¦C:²¤Æµ{¦¡ªº®Ñ¼g
  6.      With Workbooks("payment.XLSM").Sheets("2012")
  7.         .Range("A2:L65536").ClearContents
  8.         .Range("A2:L65536").Interior.Color = xlNone
  9.         .Range("A1").CurrentRegion.Offset(1) = ""                       '²M°£A1³sÄò½d³òOffset(1):²Ä¤@¦C¥H«á³sÄò½d³ò¸ê®Æ
  10.         For Each E In Files_AR                                          '°j°é¨ú¦P¤@¸ê®Æ§¨ªºÀÉ®×
  11.             Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(1)  'Offset(2)=> ¥»¨­ºâ°_¦p¬OE1-> E3
  12.             With Workbooks.Open("C:\Documents and Settings\USER\®à­±\" & E).Sheets("SHEET1")
  13.                 Set Rng(2) = .[A2:L2]
  14.                 Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)
  15.                 Rng(2).Copy Rng(1).Cells(1, -3)
  16.                 .Parent.Close False
  17.             End With
  18.          Next
  19.     End With
  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 50# GBKEE


    Option Explicit

Sub Ex()

   Dim Rng(1 To 2) As Range
   
   
     With Workbooks("payment.XLSM").Sheets("2012")
         Sheets("2012").Range("A2:L65536").ClearContents
         Sheets("2012").Range("A2:L65536").Interior.Color = xlNone

         .Range("A1").CurrentRegion.Offset(1) = ""
         
        Set Rng(1) = .[e2]

             With Workbooks.Open("C:\Documents and Settings\USER\®à­±\Connie.XLSX").Sheets("SHEET1")

             Set Rng(2) = .[A2:L2]

             Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)

             Rng(2).copy Rng(1).Cells(1, -3)
            
             .Parent.Close False

         End With
        
        Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(2)
        
         With Workbooks.Open("C:\Documents and Settings\USER\®à­±\Lily.XLSX").Sheets("SHEET1")

            Set Rng(2) = .[A2:L2]

           Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)

           Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)

            Rng(2).copy Rng(1).Cells(1, -3)

            .Parent.Close False

        End With
        
        Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(2)

         With Workbooks.Open("C:\Documents and Settings\USER\®à­±\Jane.XLSX").Sheets("SHEET1")

            Set Rng(2) = .[A2:L2]
            
            Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)

            Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)

            Rng(2).copy Rng(1).Cells(1, -3)

            .Parent.Close False

        End With
        
        Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(2)
        
         With Workbooks.Open("C:\Documents and Settings\USER\®à­±\Jenny.XLSX").Sheets("SHEET1")

            Set Rng(2) = .[A2:L2]

           Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)

           Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)

            Rng(2).copy Rng(1).Cells(1, -3)

            .Parent.Close False

        End With
        
        Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(2)

         With Workbooks.Open("C:\Documents and Settings\USER\®à­±\Patrick.XLSX").Sheets("SHEET1")

            Set Rng(2) = .[A2:L2]

           Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)

           Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)

            Rng(2).copy Rng(1).Cells(1, -3)

            .Parent.Close False

        End With
End With

End Sub

¸g¹L§ï¨}«á¡A¦¨¥\¤F¡A´N¦¨®Ä¼g¥X¨Ó»P¤j®a¤À¨É~~

TOP

¦^´_ 50# GBKEE


    ­ì¨Ó¬O³o¼Ë¼gRange("e" & ¡K§Ú´N¬O·Q¤£³q«ç¼Ë¥Î»yªkªí¹F³o¥y¸Ü!¤§«eÁÙ·QE1000¨Óªí¥Ü¡A¦ý©ñ¿ù¦b¤W¤@¥y

TOP

¦^´_ 48# 198188
·í§ÚEÄ椤¶¡¦³¤@¦CªÅ¥Õ: ¥i¥ÑÀɮש³³¡©¹¤W
  1.     Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)
  2.     Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)
½Æ»s¥N½X
¦^´_ 49# 198188
VBA ªº»¡©ú
  1. Resize ÄÝ©Ê
  2. ½Ð°Ñ¾\®M¥Î¦Ü½d¨Ò¯S©w½Õ¾ã«ü©wªº½d³ò¡C¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí½Õ¾ã«áªº½d³ò¡C
  3. expression.Resize(RowSize, ColumnSize)
  4. expression     ¥²¿ï¡C¸Ó¹Bºâ¦¡¶Ç¦^­n½Õ¾ã¤j¤pªº Range ª«¥ó¡C
  5. RowSize     ¿ï¾Ü©Êªº Variant¡C·s½d³ò¤¤©Ò¥]§tªº¦C¼Æ¡C¦pªG¬Ù²¤¦¹¤Þ¼Æ¡A½d³ò¤¤ªº¦C¼Æ«O«ù¤£ÅÜ¡C
  6. ColumnSize     ¿ï¾Ü©Êªº Variant¡C·s½d³ò¤¤©Ò¥]§tªºÄæ¼Æ¡C¦pªG¬Ù²¤¦¹¤Þ¼Æ¡A½d³ò¤¤ªºÄæ¼Æ«O«ù¤£ÅÜ¡C
½Æ»s¥N½X
  1. For xi = 1 To 5
  2.         Set Rng(2) = Rng(2).Resize(xi)
  3.         MsgBox Rng(2).Address
  4.     Next
½Æ»s¥N½X

TOP

¦^´_ 47# GBKEE


    ¥¿¦p²Ä38¶KPatrick.XLSX¡AE Äæ¥u¦³´XÄæ¸ê®Æ«ç·|¶W¥X½d³ò¡HSet Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)³o¥y¥H¤°»ò§@¬°³W«h¡H

TOP

¦^´_ 47# GBKEE


   ·P¿E¸ÑÄÀ¡A
¦ý¬O§Ú¸Õ¹L°£¤F­è¤~³o¥y Set Rng(1) = Rng(1).End(xlDown).Offset(1)¥X²{°ÝÃD¥~¡A·í§ÚEÄ椤¶¡¦³¤@¦CªÅ¥Õ¡A´N¤£À´©¹¤Ucopy¡A©Ò¥H¤~¥ÎAÄæ¡A©ú¥Õ­ì²z¡A¦ý´N¬OÂण¹L¨Ó«ç¼Ë§ï¡H

         With Workbooks.Open("C:\Documents and Settings\USER\®à­±\Patrick.XLSX").Sheets("SHEET1")

            Set Rng(2) = .[A2:L2]

            Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)

            Rng(2).Copy Rng(1).Cells(1, -3)

            .Parent.Close False

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-12-8 10:45 ½s¿è

¦^´_ 46# 198188
41# ªº¿ù»~¦b
  1.           Set Rng(2) = .[A2:L2]
  2.           Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))   'AÄæ¨S¸ê®Æ [A2].End(xlDown) ·|¨ìÀɮש³³¡
½Æ»s¥N½X
39# ¤w´£¿ô§A: µ¹§Aªºµ{¦¡½X­n®ø¤Æ¤@¤U,VBA¤~·|¶i¨B
  1.    
  2.           Set Rng(1) = .[E2]  'EÄæ¸ê®Æ¦³³sÄò
  3.          '
  4.          '
  5.            Set Rng(2) = .[A2:L2]
  6.           ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î
  7.             Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)  
  8.            '[E1].End(xlDown) ¨ìEÄ榳¸ê®Æªº¦a¤è·|°±¤î,¤~¤£·|¨ìÀɮש³³¡
  9.             Rng(2).copy Rng(1).Cells(1, -3)
½Æ»s¥N½X

TOP

¦^´_ 44# GBKEE


    Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))
AÄæ²Ä¤G¦C¶}©l©¹¤U¨ì³Ì«á¤@µ§½d³òªº¦C¼Æ ?? ¦p¤j©ó 65536-999 +1
********** ½Æ»sªº¸ê®Æ½d³ò>¶K¤W¦ì¸mªº½d³ò ?? ¨º¦h¥Xªº¸ê®Æ­nÂ\­þ¸Ì ??****
©ÎªÌ
³o¥y¥i§_§ï¦¨AÄæ²Ä¤G¦C¶}©l©¹¤U¨ì³Ì«á¤@µ§½d³òªº¦C¼Æ,¦ý°ò©ó¦³®É·|¹j¶}¤@¦C¡A¥i§_¥[¦h¥y§ä¨ì³Ì«á¤@µ§ªº¨º¦C«á¥[¨â¦C¡A¦pªG¤]¨S¦³¸ê®Æ¡A¤~½T»{¬O³Ì«á¤@µ§¡A§_«hÄ~Äò©¹¤U¶}©l

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD