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

excel vba ¦pªG§â¤GÄ檺¸ê®Æcopy¨ì¥t¤GÄæ©O?

excel vba ¦pªG§â¤GÄ檺¸ê®Æcopy¨ì¥t¤GÄæ©O?

½Ð°Ý¦U¦ì¥ý¶i:
§Ú·Q­n¥ý§âexcelªº¤GÄæ(B, C)¸ê®Æcopy¨ì¥t¤GÄæ(E, F)¡A
µM«á¡A·í¼Æ­È1©Î¼Æ­È2¤GÄæ(B, C)¥ôÀx¦s®æ¦³Åܧó®É¡A(E, F)ÄæÀx­È®æ¤º®e·|¸òµÛ§ïÅÜ¡C

§Ú¦³·Q¨ì¨Ï¥Î Worksheet_Change¤è¦¡¡A¦ý¤£ª¾­n«ç»ò¼gvba
excel.jpg
2019-2-1 17:19

¥»©«³Ì«á¥Ñ cody ©ó 2020-7-23 00:58 ½s¿è

°Ñ¦Ò¬Ý¬Ý

module:
  1. Sub copyBCtoEF()
  2.     With Worksheets("test1")
  3.         .Range("E:F").Value = .Range("B:C").Value
  4.     End With
  5. End Sub
½Æ»s¥N½X
  1. Sub doUNDO()
  2.     With Application
  3.         .EnableEvents = False
  4.         .Undo
  5.         .EnableEvents = True
  6.     End With
  7. End Sub
½Æ»s¥N½X
worksheet:
  1. Private Sub Worksheet_Change(ByVal Target As Range)

  2.     Application.EnableEvents = False
  3.    
  4.     If Target.Row = 1 Then
  5.         doUNDO
  6.         MsgBox "¤£¥i§ó§ï¼ÐÃD"
  7.         GoTo tagExit
  8.     End If
  9.    
  10.     If (Target.Column = 2 Or Target.Column = 3) And (Target.Row >= 2 Or Target.Row >= 2) And Target.Count = 1 Then
  11.         With Worksheets("test1")
  12.             .Cells(Target.Row, Target.Column + 3).Value = .Cells(Target.Row, Target.Column).Value
  13.         End With
  14.         GoTo tagExit
  15.     End If
  16.    
  17.    
  18.    
  19.     If (Target.Column = 2 Or Target.Column = 3) And (Target.Row >= 2 Or Target.Row >= 2) And Target.Count >= 2 Then
  20.    
  21.         With Worksheets("test1")
  22.             .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
  23.         End With
  24.         GoTo tagExit
  25.     End If
  26.   
  27. tagExit:
  28.     Application.EnableEvents = True

  29. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# a1234z


¹ê»Ú¹B¦æ®É¡Aµo²{·|·í¾÷¡AÀ³¸Ó¬OIJµo°ÝÃD
­×§ï¦p¥H¤Uµ{¦¡½X¡A´NOK¤F
´£¨Ñ°Ñ¦Ò¡A¦pªG¦³¨ä¥L¼gªk¡A¤]½Ð¦U¦ì«ü±Ð

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim E As Long
E = WorksheetFunction.CountA(Range("B:B")) + 1
Range("E1:F" & E).Value = Range("B1:C" & E).Value
Application.EnableEvents = True
End Sub

TOP

ª½±µE2¿é¤J =B2
¦A¦V¤U¦V¥k½Æ»s¨ìF5
µL¶·VBA¬O§_§ó²³æ

TOP

±z¦n¡A¸ÕµÛ¼g¤F£¸­Ó²³æªº¡AÅwªï°ª¤â«ü¥¿
¨ìVBA¸Ó¤u§@ªí¤U

Private Sub Worksheet_Change(ByVal Target As Range)
Dim E As Long
E = WorksheetFunction.CountA(Range("B:B")) + 1
Range("E1:F" & E).Value = Range("B1:C" & E).Value
End Sub

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD