Hsieh

¥i¯à­«·s§G¸p¤@¤U·|¦n¤@ÂI
·s¼W¤@¯ë¼Ò²Õ
¿é¤Jµ{¦¡½X

1. Public i%
   
2. Sub auto_open()
   
3. With Sheet2
   
4. k = .[IV1].End(xlToLeft).Column
   
5. i = IIf(k = 1, 0, k - 3)
   
6. If i = 0 Then .[A1:C1].Value = Sheet1.[A1:C1].Value
   
7. End With
   
8. End Sub

½Æ»s¥N½X

Sheet1¼Ò²Õ

1. Private Sub Worksheet_Calculate() '³o¬OSheet1¹w³]ªº­«ºâIJ°Ê¨Æ¥óµ{¦¡
   
2.     Dim A As Range
   
3.     'Static ³¯­z¦¡  ¦bµ{§Ç¼h¦¸¤¤¥Î¨Ó«Å§iÅܼƨðt¸mÀx¦sªÅ¶¡¡C¥H Static ³¯­z¦¡«Å§iªºÅܼơA¦bµ{¦¡°õ¦æ´Á¶¡¡A·|¤@ª½«O¯d¤º®e¡C
   
4.      With Sheet2
   
5.      Set A = .Cells(65536, i + 1).End(xlUp).Offset(1).Resize(, 3)
   
6.      If A.Row > 100 Then i = i + 3: .Cells(1, i + 1).Resize(, 3) = [A1:C1].Value '¨ì²Ä100¦C®É¥k²¾3Äæ
   
7.      Set A = .Cells(65536, i + 1).End(xlUp).Offset(1).Resize(, 3)
   
8.        If A.Cells(1, 3).Offset(-1) <> [C2] Then   '¦¨¥æ»ù¦³²§°Ê®Éªº±ø¥ó¦¨¥ß®É
   
9.             A.Value = [A2:C2].Value                '±NShse1ªº¤é´Á,®É¶¡,¦¨¥æ»ù°O¿ý¨ìSheet2
   
10.        End If
    
11.     End With
    
12. End Sub

½Æ»s¥N½X

¬ö¿ý.zip (10.81 KB)

¤U¸ü¦¸¼Æ: 14

2010-11-16 22:58

Hsieh

¦¹®ÉªºDDE¬O§_ÁÙ¥¼¶Ç¤J¼Æ¾Ú³y¦¨A2:C2¥X²{¿ù»~­È©Ò­P

1. Private Sub Worksheet_Calculate() '³o¬OSheet1¹w³]ªº­«ºâIJ°Ê¨Æ¥óµ{¦¡
   
2.     Dim A As Range
   
3.     'Static ³¯­z¦¡  ¦bµ{§Ç¼h¦¸¤¤¥Î¨Ó«Å§iÅܼƨðt¸mÀx¦sªÅ¶¡¡C¥H Static ³¯­z¦¡«Å§iªºÅܼơA¦bµ{¦¡°õ¦æ´Á¶¡¡A·|¤@ª½«O¯d¤º®e¡C
   
4.      With Sheet2
   
5.      Set A = .Cells(65536, i + 1).End(xlUp).Offset(1).Resize(, 3)
   
6.      If A.Row > 100 Then i = i + 3: .Cells(1, i + 1).Resize(, 3) = [A1:C1].Value '¨ì²Ä100¦C®É¥k²¾3Äæ
   
7.      Set A = .Cells(65536, i + 1).End(xlUp).Offset(1).Resize(, 3)
   
8.      If IsError([C2].Value) Then Exit Sub
   
9.        If A.Cells(1, 3).Offset(-1) <> [C2] Then   '¦¨¥æ»ù¦³²§°Ê®Éªº±ø¥ó¦¨¥ß®É
   
10.             A.Value = [A2:C2].Value                '±NShse1ªº¤é´Á,®É¶¡,¦¨¥æ»ù°O¿ý¨ìSheet2
    
11.        End If
    
12.     End With
    
13. End Sub

½Æ»s¥N½X

Hsieh

§A¸ÕµÛ¿ý»sA2:C2´¡¤JÀx¦s®æ
¦A¶ñ¤J´N¦æ¤F

Hsieh

¦^´_ 14# aries

1. Private Sub Worksheet_Calculate() '³o¬OSheet1¹w³]ªº­«ºâIJ°Ê¨Æ¥óµ{¦¡
   
2.     Dim A As Range
   
3.      With Sheet2
   
4.      .[A2:C2].Insert xlShiftDown
   
5.      Set A = .Cells(2, 1).Resize(, 3)
   
6.      If A.Row > 100 Then i = i + 3: .Cells(1, i + 1).Resize(, 3) = [A1:C1].Value '¨ì²Ä100¦C®É¥k²¾3Äæ
   
7.      Set A = .Cells(2, i + 1).Resize(, 3)
   
8.      If IsError([C2].Value) Then Exit Sub
   
9.        If .[C3] <> [C2] Then   '¦¨¥æ»ù¦³²§°Ê®Éªº±ø¥ó¦¨¥ß®É
   
10.             A.Value = [A2:C2].Value '±NShse1ªº¤é´Á,®É¶¡,¦¨¥æ»ù°O¿ý¨ìSheet2
    
11.             Else
    
12.             .[A2:C2].Delete xlShiftUp '¥¼²§°Ê»ù®æ´N§R°£´¡¤Jªº
    
13.        End If
    
14.     End With
    
15. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 16# aries

1. Private Sub Worksheet_Calculate() '³o¬OSheet1¹w³]ªº­«ºâIJ°Ê¨Æ¥óµ{¦¡
   
2.     Dim A As Range
   
3.      If IsError([C2].Value) Then Exit Sub
   
4.      With Sheet2
   
5.      i = .[IV1].End(xlToLeft).Column - 2
   
6.        'If .[C3] <> [C2] Then   '¦¨¥æ»ù¦³²§°Ê®Éªº±ø¥ó¦¨¥ß®É
   
7.           If .Cells(65536, i).End(xlUp).Row = 100 Then i = i + 3: .Cells(1, i).Resize(, 3).Value = [A1:C1].Value
   
8.           .Cells(2, i).Resize(, 3).Insert xlShiftDown
   
9.           .Cells(2, i).Resize(, 3) = [A2:C2].Value '±NShse1ªº¤é´Á,®É¶¡,¦¨¥æ»ù°O¿ý¨ìSheet2
   
10.        'End If
    
11.     End With
    
12. End Sub

½Æ»s¥N½X