198188

¦^´_ 44# GBKEE


    Set Rng(1) = .[E1000].End(xlUp):  E1000¶}©l©¹¤WÀˬd³Ì«á¤@¦C=> ¦p¬O E999
¨º»ò½Ð°Ý§ÚÀ³¸Ó¦p¦ó¼g³o¥y¡A±N¥¦¼g¦¨set rng(1) = ·í«eworksheet(2012)E Ä檺³Ì«á¤@¦C¥[1¡H
¨ä¹ê§ÚAÄæ¤j³¡¤À³£¨S¦³¸ê®Æ

GBKEE

¦^´_ 43# 198188
Set Rng(1) = .[E1000].End(xlUp):  E1000¶}©l©¹¤WÀˬd³Ì«á¤@¦C=> ¦p¬O E999
Set Rng(1) = .[E1000].End(xlUp).Offset(, -4) => A999
¨º A999 ¨ì Àɮש³³¡ ªº¦C¼Æ¬O 2003-> 65536-999 +1
*********
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Â\­þ¸Ì ??****

198188

¦^´_ 42# GBKEE


    01.Option Explicit

02.Sub Ex()

03.    Dim Rng(1 To 2) As Range

04.    With Workbooks("payment.XLSM").Sheets("2012")

05.        Set Rng(1) = .[E1000].End(xlUp).Offset(, -4) '³o Rng(1)ªº¦ì¸m

06.        With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")

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

08.            Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))

09.            MsgBox .Rows.Count - Rng(2).Rows.Count < .Rows.Count - Rng(1).Row 'True: Rng(2)½d³ò¤j©ó Rng(1) ´N¦³¿ù»~

10.            Rng(2).Copy Rng(1)

11.            .Parent.Close False

12.        End With

13.    End With

14.End Sub
¬ORng(2)½d³ò¤j©ó Rng(1)¡A¦ý¬OSet Rng(1) = .[E1000].End(xlUp).Offset(, -4) '³o Rng(1)ªº¦ì¸m ¡]³o¥yªº·N«ä¬O¤£¬Oµ¥©ó¥ÑE1000¶}©l©¹¤WÀˬd³Ì«á¤@¦C¡AOFFSET( , -4)±NEÄæ§ï¦¨AÄæ
Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))¬O§_·N«ä±qAÄæ²Ä¤G¦C¶}©l©¹¤U¨ì³Ì«á¤@µ§¡H

GBKEE

¦^´_ 40# 198188
With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")
±N¦¹ÀÉ®×»P¥¼¥X¿ùÀÉ®×¹ï½Õ¬Ý¬O§_¤@¼Ëªº¥X¿ù,¥i¨Ì¤U¤èµ{§Ç¬Ý¬Ý

¦^´_ 41# 198188

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng(1 To 2) As Range
   
4.     With Workbooks("payment.XLSM").Sheets("2012")
   
5.         Set Rng(1) = .[E1000].End(xlUp).Offset(, -4) '³o Rng(1)ªº¦ì¸m
   
6.         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")
   
7.             Set Rng(2) = .[A2:L2]
   
8.             Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))
   
9.             MsgBox .Rows.Count - Rng(2).Rows.Count < .Rows.Count - Rng(1).Row 'True: Rng(2)½d³ò¤j©ó Rng(1) ´N¦³¿ù»~
   
10.             Rng(2).Copy Rng(1)
    
11.             .Parent.Close False
    
12.         End With
    
13.     End With
    
14. End Sub

½Æ»s¥N½X

198188

¤U¸ü (83.95 KB)

2012-12-8 00:57

¤U¸ü (84.9 KB)

2012-12-8 00:57

¤U¸ü (89.76 KB)

2012-12-8 00:57

½Ð°Ý­þ¸Ì¥X¿ù¤F¡HRng(2).copy Rng(1)

198188

¦^´_ 39# GBKEE


    Option Explicit

Sub Ex() 'µ{§Ç¦WºÙ¤£­n¥Î copy ³o¬Ovba¤èªkªºÃöÁä¦r


   Dim Rng(1 To 2) As Range

     With Workbooks("payment.XLSM").Sheets("2012")

         .Range("A1").CurrentRegion.Offset(1) = ""

        Set Rng(1) = .[E2]  'EÄæ¸ê®Æ¦³³sÄò

       'MsgBox Rng(1).Cells(1, -3).Address '¦^¨ìAÄæ

         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Connie.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)   'AÄæ

            .Parent.Close False

         End With

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

         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Lily.XLSX").Sheets("SHEET1")

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

           ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î

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

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

            .Parent.Close False

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

         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jane.XLSX").Sheets("SHEET1")

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

           ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î

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

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

            .Parent.Close False

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

         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jenny.XLSX").Sheets("SHEET1")

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

           ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î

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

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

            .Parent.Close False

        End With
        
        Set Rng(1) = Rng(1).End(xlDown).Offset(1)     'µ{¦¡run ¨ì³o¸Ì¥X²{°ÝÃD application-defined or object-defined error

         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")

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

           ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î

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

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

            .Parent.Close False

        End With
End With

End Sub
³Ì«á¤@­Ó¥X²{°ÝÃD
   Set Rng(1) = Rng(1).End(xlDown).Offset(1)     'µ{¦¡run ¨ì³o¸Ì¥X²{°ÝÃD application-defined or object-defined error
¥t¥~¤¤¶¡¦³¤@¦CªÅ®æ¡A¤§«áªº¸ê®Æ´NµLªk¥X¨Ó¡C¦ý²Ä¤@¦¸ªºµ{¦¡¡A¦bcopy ²Ä¤@­Óexcel ´Nºâ¤¤¶¡¦³¤@¦CªÅ®æ¡A¥¦¤]¥i¥H©¹¤Ucopy?

GBKEE

¦^´_ 38# 198188

1. Option Explicit
   
2. Sub Ex() 'µ{§Ç¦WºÙ¤£­n¥Î copy ³o¬Ovba¤èªkªºÃöÁä¦r
   
3.    Dim Rng(1 To 2) As Range
   
4.      With Workbooks("payment.XLSM").Sheets("2012")
   
5.          .Range("A1").CurrentRegion.Offset(1) = ""
   
6.         Set Rng(1) = .[E2]  'EÄæ¸ê®Æ¦³³sÄò
   
7.        'MsgBox Rng(1).Cells(1, -3).Address '¦^¨ìAÄæ
   
8.          With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jenny.XLSX").Sheets("SHEET1")
   
9.              Set Rng(2) = .[A2:L2]
   
10.              Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
    
11.              Rng(2).copy Rng(1).Cells(1, -3)   'AÄæ
    
12.             .Parent.Close False
    
13.          End With
    
14.         Set Rng(1) = Rng(1).End(xlDown).Offset(1)
    
15.          With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jane.XLSX").Sheets("SHEET1")
    
16.             Set Rng(2) = .[A2:L2]
    
17.            ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î
    
18.             Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
    
19.             Rng(2).copy Rng(1).Cells(1, -3)
    
20.             .Parent.Close False
    
21.         End With
    
22.         '
    
23.         '
    
24.         ' ¥H¤U¦P
    
25.         '
    
26.         '
    
27.         
    
28. 
    
29.       End With
    
30. End Sub

½Æ»s¥N½X

198188

¦^´_ 35# GBKEE

¤U¸ü (55.92 KB)
Connie

2012-12-7 13:56

¤U¸ü (29.32 KB)
Jane

2012-12-7 13:56

¤U¸ü (53.26 KB)
Jenny

2012-12-7 13:56

¤U¸ü (31.53 KB)
Lily

2012-12-7 13:56

¤U¸ü (44.76 KB)
Patrick

2012-12-7 13:56

¤U¸ü (57.71 KB)
Result

2012-12-7 13:56

Option Explicit
Sub copy()
   Dim Rng(1 To 2) As Range
    'With Workbooks.Open("C:\Users\patrick.HKG\Desktop\COPY.XLSM").Sheets("2012")
    With Workbooks("payment.XLSM").Sheets("2012")                     
        .Range("A1").CurrentRegion.Offset(1) = ""               
        Set Rng(1) = .[a2]                                      
         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jenny.XLSX").Sheets("SHEET1")   
             Set Rng(2) = .[A2:L2]
             Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))     
             Rng(2).copy Rng(1)
            .Parent.Close False                                 
         End With
        Set Rng(1) = Rng(1).End(xlDown).Offset(1)               
         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jane.XLSX").Sheets("SHEET1")     
            Set Rng(2) = .[A2:L2]
            Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))   
            Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
            Rng(2).copy Rng(1)
            .Parent.Close False                                   
        End With
         Set Rng(1) = Rng(1).End(xlDown).Offset(1)              
         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Lily.XLSX").Sheets("sheet1")   
             Set Rng(2) = .[A2:L2]                          
       Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))   
            Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
             Rng(2).copy Rng(1)
            .Parent.Close False                                 
         End With
        Set Rng(1) = Rng(1).End(xlDown).Offset(1)              
         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Connie.XLSX").Sheets("sheet1")                 Set Rng(2) = .[A2:L2]                          
       Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))   
            Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
             Rng(2).copy Rng(1)
            .Parent.Close False                  
         End With
        Set Rng(1) = Rng(1).End(xlDown).Offset(1)              
         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("sheet1")   
             Set Rng(2) = .[A2:L2]                          
       Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))   
            Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)
             Rng(2).copy Rng(1)
            .Parent.Close False                                 
         End With
    End With
End Sub
  §Ú¥Î¤F¤W­±ªºµ{¦¡¡A¦ý¥X¨Óªºresult«oµLªk¥þ³¡¥X¨Ó¡C½Ð°Ý­þ¸Ì»Ý­n§ï¡H

198188

¦^´_ 35# GBKEE


    Set Rng(1) = .[A2]
         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jenny.XLSX").Sheets("SHEET1")
              Set Rng(2) = .[A2:AL2]
       Set Rng(2) = .Range(Rng(2), .[e2].End(xlDown))

   

             Rng(2).copy Rng(1)

            .Parent.Close False

        End With

         
         Set Rng(1) = Rng(1).End(xlDown).Offset(1)
         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Jane.XLSX").Sheets("SHEET1")
            Set Rng(2) = .[A2:AL2]

            Set Rng(2) = .Range(Rng(2), .[AL2].End(xlDown))

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

            Rng(2).copy Rng(1)

            .Parent.Close False
        End With
·|¤£·|¦]¬°¤§«e¬O¥ÎAÄæ¨Ó­pºâ³Ì«á¤@µ§¡A¦ý²{¦b§Ú§ï¦¨¥ÎEÄæÀˬd³Ì«á¤@µ§¡A¦Ó Set Rng(1) = Rng(1).End(xlDown).Offset(1)³o¥y¸Ü¬O¥ÎAÄæ¨Ó³]©w¡H
Set Rng(1) = .[A2]
Set Rng(1) = Rng(1).End(xlDown).Offset(1)

198188

¦^´_ 35# GBKEE


    ³o»ò§Ö¨ì©³³¡¶Ü¡H§Úªº¸ê®Æ¤~´X¤Q¦C¡H