Worksheet Change事件未复制和粘贴数据。

huangapple
go评论143阅读模式
英文:

Worksheet Change event not copying and pasting data

问题

以下是您的代码的翻译部分：

  1. 我的当前代码如下。
  3. 我有一个工作簿中的两个工作表。
  5. 1.) 名称为 - 数据和粘贴的工作表
  7. 2.) 数据工作表受保护。从单元格J3：O1000开始，列J到O未受保护。行1和2保留为标题。
  9. 3.) 当列N中有**YES**时，范围J:O会自动排序，下面的代码会帮助排序，而No将始终在YES之后，在末尾是空白单元格。
  11. 4.) 这个代码可以工作，但它没有将显示为列N中有YES的J:O行复制并粘贴为值。
  13. 我无法理解为什么它不起作用。TIA
  15. Private Sub Worksheet_Change(ByVal Target As Range)
  16.     Dim KeyCells As Range
  17.     Dim lastRow As Long
  18.     Dim DataSheet As Worksheet
  19.     Dim PastedSheet As Worksheet
  20.     Dim sourceRange As Range
  21.     Dim destinationRow As Long
  22.     Dim isDataSheetUnprotected As Boolean
  23.     
  24.     Application.EnableEvents = False
  25.     
  26.     Set DataSheet = ThisWorkbook.Sheets("Data")
  27.     Set PastedSheet = ThisWorkbook.Sheets("Pasted")
  28.     
  29.     Set KeyCells = Range("N3:N" & Cells(Rows.Count, "N").End(xlUp).Row)
  30.     
  31.     If Not Application.Intersect(KeyCells, Target) Is Nothing Then
  32.         lastRow = DataSheet.Cells(DataSheet.Rows.Count, "J").End(xlUp).Row
  33.         
  34.         isDataSheetUnprotected = False
  35.         If DataSheet.ProtectContents Then
  36.             DataSheet.Unprotect Password:="Mama, I'm Coming Home"
  37.             isDataSheetUnprotected = True
  38.         End If
  39.         
  40.         DataSheet.Range("J3:O" & lastRow).Sort Key1:=DataSheet.Range("N3:N" & lastRow), _
  41.             Order1:=xlDescending, Header:=xlNo
  42.         
  43.         If UCase(Target.Value) = "YES" Then
  44.             MsgBox "复制第 " & Target.Row & " 行的数据"
  45.             destinationRow = PastedSheet.Cells(PastedSheet.Rows.Count, "A").End(xlUp).Row + 1
  46.             MsgBox "粘贴工作表中的目标行: " & destinationRow
  47.             Set sourceRange = DataSheet.Range("J" & Target.Row & ":K" & Target.Row)
  48.             sourceRange.Copy
  49.             DataSheet.Range("A" & destinationRow).PasteSpecial xlPasteValues
  50.             MsgBox "数据已复制并粘贴"
  51.             Application.CutCopyMode = False
  52.         End If
  53.         
  54.         If isDataSheetUnprotected Then
  55.             DataSheet.Protect Password:="Mama, I'm Coming Home"
  56.         End If
  57.     End If
  58.     
  59.     Application.EnableEvents = True
  60. End Sub

希望这可以帮助您理解代码的内容。如果您有任何其他问题，请随时提问。

英文:

My present code is below.

I have two sheets in a workbook.

1.) Sheet Named - Data and Pasted

2.) Data Sheet is protected. Columns J to O are unprotected, Starting from cell J3:O1000. Rows 1 and 2 are reserved for headers.

3.) The range J:O automatically sorts with the help of the below code whenever there is YES in column N , while No will always be after YES, in the end blanks.

4.) This is working but it is not copying and pasting as values of those rows from J:O where it shows YES in column N

I can't understand why it is not working. TIA

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim KeyCells As Range
  3.     Dim lastRow As Long
  4.     Dim DataSheet As Worksheet
  5.     Dim PastedSheet As Worksheet
  6.     Dim sourceRange As Range
  7.     Dim destinationRow As Long
  8.     Dim isDataSheetUnprotected As Boolean
  9.     
  10.     Application.EnableEvents = False
  11.     
  12.     Set DataSheet = ThisWorkbook.Sheets("Data")
  13.     Set PastedSheet = ThisWorkbook.Sheets("Pasted")
  14.     
  15.     Set KeyCells = Range("N3:N" & Cells(Rows.Count, "N").End(xlUp).Row)
  16.     
  17.     If Not Application.Intersect(KeyCells, Target) Is Nothing Then
  18.         lastRow = DataSheet.Cells(DataSheet.Rows.Count, "J").End(xlUp).Row
  19.         
  20.         isDataSheetUnprotected = False
  21.         If DataSheet.ProtectContents Then
  22.             DataSheet.Unprotect Password:="Mama, I'm Coming Home"
  23.             isDataSheetUnprotected = True
  24.         End If
  25.         
  26.         DataSheet.Range("J3:O" & lastRow).Sort Key1:=DataSheet.Range("N3:N" & lastRow), _
  27.             Order1:=xlDescending, Header:=xlNo
  28.         
  29. If UCase(Target.Value) = "YES" Then
  30.     MsgBox "Copying data for row " & Target.Row
  31.     destinationRow = PastedSheet.Cells(PastedSheet.Rows.Count, "A").End(xlUp).Row + 1
  32.     MsgBox "Destination row in Pasted sheet: " & destinationRow
  33.     Set sourceRange = DataSheet.Range("J" & Target.Row & ":K" & Target.Row)
  34.     sourceRange.Copy
  35.     DataSheet.Range("A" & destinationRow).PasteSpecial xlPasteValues
  36.     MsgBox "Data copied and pasted"
  37.     Application.CutCopyMode = False
  38. End If
  39.         
  40.         If isDataSheetUnprotected Then
  41.             DataSheet.Protect Password:="Mama, I'm Coming Home"
  42.         End If
  43.     End If
  44.     
  45.     Application.EnableEvents = True
  46. End Sub

答案1

得分: 1

Your code is pasting the data on the data sheet instead of on the pasted sheet. I've changed the line as needed:

  1. If UCase(Target.Value) = "YES" Then
  2.     MsgBox "Copying data for row " & Target.Row
  3.     destinationRow = PastedSheet.Cells(PastedSheet.Rows.Count, "A").End(xlUp).Row + 1
  4.     MsgBox "Destination row in Pasted sheet: " & destinationRow
  5.     Set sourceRange = DataSheet.Range("J" & Target.Row & ":K" & Target.Row)
  6.     sourceRange.Copy
  7.     PastedSheet.Range("A" & destinationRow).PasteSpecial xlPasteValues 'changed this line
  8.     MsgBox "Data copied and pasted"
  9.     Application.CutCopyMode = False
  10. End If
英文:

Your code is pasting the data on the data sheet instead of on the pasted sheet. I've changed the line as needed:

  1. If UCase(Target.Value) = "YES" Then
  2.     MsgBox "Copying data for row " & Target.Row
  3.     destinationRow = PastedSheet.Cells(PastedSheet.Rows.Count, "A").End(xlUp).Row + 1
  4.     MsgBox "Destination row in Pasted sheet: " & destinationRow
  5.     Set sourceRange = DataSheet.Range("J" & Target.Row & ":K" & Target.Row)
  6.     sourceRange.Copy
  7.     PastedSheet.Range("A" & destinationRow).PasteSpecial xlPasteValues 'changed this line
  8.     MsgBox "Data copied and pasted"
  9.     Application.CutCopyMode = False
  10. End If

huangapple
  • 本文由 发表于 2023年8月10日 21:46:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/76876351.html
  • excel
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定