当单元格值更改时，将单元格值更改所在的列复制到相同范围的另一张工作表 [英] When a cell value changes, copy the column from where the cell value changed to another sheet at the same range

问题描述

例如，如果仅在A:A范围内更改单元格A8，则复制D4:D8并将其作为值粘贴到工作表"ADP"中的同一位置，即D4:D8.

For example, if in the range of A:A only cell A8 change then copy D4:D8 and paste it as value in sheet "ADP" at the same place i.e. D4:D8.

为此，我尝试了以下宏

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.range = "A:A" Then
        Call copy_paste_as_value
    End If
End Sub


Sub copy_paste_as_value()
    Range("d4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Sheets("ADP").Activate
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C4").Select
    Application.CutCopyMode = False
End Sub

我只想复制单元格值发生变化的数据，但是它将整个表复制到另一张纸上.

I want to copy only that data against which cell value changes, but it copies the whole table to another sheet.

对我来说，主要问题是弄清楚哪个单元格发生了更改，并仅从哪个单元格值发生了更改中复制该列中的数据.

main issue for me is to figure out which cell changed and copy data from that column only from which cell value changed.

这里，要注意的是，只有在A:A范围内有变化，并且不需要复制粘贴以外的其他任何单元格中的变化时，才应该复制数据.

here, it's to be noted that data should be copied only if there is change in range A:A, if change in any other cell than copy paste not required.

将提供任何帮助. 谢谢.

any help will be appriciated. thank you.

推荐答案

您可以尝试:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsSou As Worksheet, wsDes As Worksheet

    'Set the worksheets to avoid conflicts
    Set wsSou = Target.Worksheet
    Set wsDes = ThisWorkbook.Worksheets("ADP")

    If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Count = 1 Then

        wsDes.Range(wsDes.Cells(Target.Row, 4), wsDes.Cells(Target.Row, 9)).Value = wsSou.Range(wsSou.Cells(Target.Row, 4), wsSou.Cells(Target.Row, 9)).Value

    End If

End Sub

这篇关于当单元格值更改时，将单元格值更改所在的列复制到相同范围的另一张工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！