yen956

¸Õ¸Õ¬Ý!
¤U¦CVBA©ñ¨ìSheet1("Sheet2")ªºvba¤¤,
¤£­n©ñModule1¤¤

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim Rng As Range, LastR As Integer, sh3 As Object
   
3.     Set sh3 = Sheets("Sheet3")
   
4.     Set Rng = [C13]       '³]©w [C13] ¬° Worksheet_ChangeIJ°Ê½d³ò
   
5.     LastR = sh3.[A65536].End(xlUp).Row + 1       '¨ú±o ÄæA³Ì¤U­±«DªÅ¥Õ®æªº¤U¤@®æ ªº¦C¸¹
   
6.     If Not Intersect(Target, Rng) Is Nothing And Rng.Value > 50 Then
   
7.         [A17].Resize(1, 4).Select
   
8.         [A17].Resize(1, 4).Copy sh3.Cells(LastR, 1)
   
9.     End If
   
10. End Sub

½Æ»s¥N½X

yen956

Private Sub Worksheet_Change(ByVal Target As Range)
    '°²©w "C13" ªº¤½¦¡¬° =[A1]+[B1]+[E1]
    '«h Change ªº Target ¬° [A1] or [B1] or [E1]
    Dim Rng As Range, LastR As Integer, sh3 As Object
    Set sh3 = Sheets("Sheet3")
    Set Rng = Union([A1:B1], [E1])      '³]©w Worksheet_ChangeIJ°Ê½d³ò(»P¤½¦¡¦³ÃöªºRange­n¥þ³¡©ñ¶i¥h)
    LastR = sh3.[A65536].End(xlUp).Row + 1       '¨ú±o "Sheet3" ÄæA³Ì¤U­±«DªÅ¥Õ®æªº¤U¤@®æ ªº¦C¸¹
    If Not Intersect(Target, Rng) Is Nothing Then
        If [C13] > 50 Then
            [A17].Resize(1, 4).Select
            [A17].Resize(1, 4).Copy sh3.Cells(LastR, 1)
        End If
    End If
End Sub

yen956

¦^´_ 5# peter95
¸Õ¸Õ¬Ý!
±N
     [A17].Resize(1, 4).Copy sh3.Cells(LastR, 1)
§ï¦¨      
     [A17].Resize(1, 4).Copy
        sh3.Cells(LastR, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

©Î
±N¾ã­ÓWorksheet_Change()§R°£
§ï¦¨
Private Sub Worksheet_Calculate()
    Dim Rng As Range, LastR As Integer, sh3 As Object
    Set sh3 = Sheets("Sheet3")
    LastR = sh3.[A65536].End(xlUp).Row + 1       '¨ú±o "Sheet3" ÄæA³Ì¤U­±«DªÅ¥Õ®æªº¤U¤@®æ ªº¦C¸¹
    If [C13] > 50 Then
        [A17].Resize(1, 4).Copy
        sh3.Cells(LastR, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
End Sub
¸Õ¸Õ¬Ý!

yen956

©êºp, E-mail §Ú¤£·|, ¥i¥tµo¤å½Ð±Ð¨ä¥L¤j¤j,
¨C2¤ÀÄÁ«h¥i­É¥Î Hsiehª©¤jªº onTime, ¦p¤U:

Worksheet_Calculate §R°£, §ï¥Î Hsiehª©¤jªº onTime
½Ð©ñ¦b Module
http://forum.twbts.com/thread-19283-1-2.html
'±q¦­¤W8ÂI¨ì¤U¤È5ÂI¨C2¤ÀÄÁ°õ¦æ "Copy_test" 1¦¸
Sub OnTime_test()
    Dim t
    For t = TimeValue("08:00:00") To TimeValue("17:00:00") Step TimeValue("00:02:00")
       Application.OnTime t, "Copy_test"
    Next
End Sub

Sub Copy_TEST()
    Dim LastR As Integer, sh2 As Object, sh3 As Object
    Set sh2 = Sheets("Sheet2")
    Set sh3 = Sheets("Sheet3")
    LastR = sh3.[A65536].End(xlUp).Row + 1       '¨ú±o "Sheet3" ÄæA³Ì¤U­±«DªÅ¥Õ®æªº¤U¤@®æ ªº¦C¸¹
    If sh2.[C13] > 50 Then
        sh2.[A17].Resize(1, 4).Copy
        sh3.Cells(LastR, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
    End If
End Sub

yen956

¦³­«·s­pºâ´N·|IJ°Ê Worksheet_Calculate, µLªk©µ«á2¤ÀÄÁ,
¬G§ï¥Î¨C2¤ÀÄÁÀˬd¤@¦¸(¤£¬O°O¿ý¤@¦¸), ¨Æ¹ê¤W¦³®t¶Ü?
§Aªº·N«ä¬O µo²{ [C13] > 50 ´N©µ«á2¤ÀÄÁ°O¿ý,
³o¸ò¨C2¤ÀÄÁÀˬd¤@¦¸(¤£¬O°O¿ý¤@¦¸), µo²{ [C13] > 50 ¦A°O¿ý,
¦³®t¶Ü?

yen956

¦^´_ 12# peter95
¬Oªº, ¨C2¤ÀÄÁÀˬd¤@¦¸©M©µ«á2¤ÀÄÁ°O¿ýªº½T¤£¤@¼Ë,
¦³¥i¯à¨C­ÓÀˬdÂI³£¬O§CÂI,¦Ó¿ù¹L°ªÂI
´À¥N¤è®×:
·s¼W¼È¦sªí sheet4, ±N Worksheet_Calculate ªºµ²ªG¥þ³¡¼È©ñ¼È¦sªí sheet4,
¦A¨C2¤ÀÄÁ±q¼È¦sªí sheet4 ¤¤¿ï¨ú [C13] ³Ì°ª­È¨º¦C
½Æ»s¨ì Sheet3, ¦p¦ó?

'©ñModule
'­É¥Î Hsiehª©¤jªº onTime, ½Ð©ñ¦b Module
'http://forum.twbts.com/thread-19283-1-2.html
'±q¦­¤W8ÂI¨ì¤U¤È5ÂI¨C2¤ÀÄÁ°õ¦æ "Copy_test" 1¦¸
Sub OnTime_test()
    Dim t
    For t = TimeValue("08:00:00") To TimeValue("17:00:00") Step TimeValue("00:02:00")
       Application.OnTime t, "Copy_test"
    Next
End Sub

Sub Copy_TEST()
    Dim LstR3 As Integer, LstR4 As Integer, sh3 As Object, sh4 As Object
    Set sh3 = Sheets("Sheet3")
    Set sh4 = Sheets("Sheet4")
    LstR3 = sh3.[A65536].End(xlUp).Row + 1       '¨ú±o "Sheet3" ÄæA³Ì¤U­±«DªÅ¥Õ®æªº¤U¤@®æ ªº¦C¸¹
    LstR4 = sh4.[A65536].End(xlUp).Row            '¨ú±o "Sheet4" ÄæF³Ì¤U­±«DªÅ¥Õ®æªº¦C¸¹
    If sh4.[A1] = "" Then Exit Sub
    '«ö sh4.[F1] ­°¾­±Æ§Ç
    sh4.[A1].Resize(LstR4, 6).Select
    Selection.Sort _
        Key1:=sh4.[F1], Order1:=xlDescending, _
        Header:=xlNo
    sh4.[A1].Resize(1, 4).Copy sh3.Cells(LstR3, 1)
    '²M°£sheet4, ­«·s¨Ñ Worksheet_Calculate ¼È¦s
    sh4.Cells() = ""
End Sub

'¤U­±¦P¼Ë©ñ Sheet2
Private Sub Worksheet_Calculate()
    Dim Rng As Range, LstR As Integer, sh4 As Object
    Set sh4 = Sheets("Sheet4")
    If Not Application.IsNumber([C13]) Then Exit Sub    '¨S¸ê®Æ´N¸õ¥X
    LstR4 = sh4.[A65536].End(xlUp).Row      '¨ú±o "Sheet4" ÄæA³Ì¤U­±«DªÅ¥Õ®æªº¦C¸¹
    If [C13] > 50 Then
        [A17].Resize(1, 4).Copy
        sh4.Cells(LstR4, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        [C13].Copy                         '[C13]ªº­È¤]«O¯d¨ìSheet4ÄæF
        sh4.Cells(LstR4, 6).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End If
End Sub