cody

參考看看

module:

1. Sub copyBCtoEF()
   
2.     With Worksheets("test1")
   
3.         .Range("E:F").Value = .Range("B:C").Value
   
4.     End With
   
5. End Sub

複製代碼

1. Sub doUNDO()
   
2.     With Application
   
3.         .EnableEvents = False
   
4.         .Undo
   
5.         .EnableEvents = True
   
6.     End With
   
7. End Sub

複製代碼

worksheet:

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. 
   
3.     Application.EnableEvents = False
   
4.    
   
5.     If Target.Row = 1 Then
   
6.         doUNDO
   
7.         MsgBox "不可更改標題"
   
8.         GoTo tagExit
   
9.     End If
   
10.    
    
11.     If (Target.Column = 2 Or Target.Column = 3) And (Target.Row >= 2 Or Target.Row >= 2) And Target.Count = 1 Then
    
12.         With Worksheets("test1")
    
13.             .Cells(Target.Row, Target.Column + 3).Value = .Cells(Target.Row, Target.Column).Value
    
14.         End With
    
15.         GoTo tagExit
    
16.     End If
    
17.    
    
18.    
    
19.    
    
20.     If (Target.Column = 2 Or Target.Column = 3) And (Target.Row >= 2 Or Target.Row >= 2) And Target.Count >= 2 Then
    
21.    
    
22.         With Worksheets("test1")
    
23.             .Range(.Cells(Target.Row, Target.Column + 3), .Cells(Target.Row + UBound(Target.Value2, 1) - 1, Target.Column + UBound(Target.Value2, 2) - 1 + 3)).Value = Target.Value2
    
24.         End With
    
25.         GoTo tagExit
    
26.     End If
    
27.   
    
28. tagExit:
    
29.     Application.EnableEvents = True
    
30. 
    
31. End Sub

複製代碼