Excel VBA对单元格的更改进行剪切或粘贴

I am making an inventory system.What i want is to copy closing stock to opening stock column when date will change and closing stock keep its formula just copy values to opening stock.

我正在做一个库存系统。我想要的是将收市股票复制到开市股票栏目当日期改变时，收市股票保持它的公式只是将值复制到开市股票。

Date is in cell "AF1" with today date formula =Today() Closing Stock is in Column AB2:AB75 with formula(Opening Stock - Sale = Closing stock)

日期在单元“AF1”中，日期公式为:今天()收盘股票在第AB2栏:AB75，公式为(开盘价-卖出=收盘价)

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target = Range("AF1") Then
    Range("AB2:AB75").Copy
    Range("AA2:AA75").PasteSpecial
 Else

 End If
 End Sub

when i change date it crash TYPE MISMATCH 13 and This become highlight yellow

当我更改日期时，它崩溃类型不匹配13，这变成突出显示的黄色

If Target = Range("AF1") Then

如果目标= Range("AF1")则

2 个解决方案

#1

If you are going to change anything within a Worksheet_Change event macro, you need to disable event handling so that the sub does not trigger another event and try to run on top of itself.

如果您要在Worksheet_Change事件宏中更改任何内容，您需要禁用事件处理，以便子程序不会触发另一个事件，并尝试在其自身之上运行。

Additionally, Target can be one cell or a large number of cells. You cannot reliably compare it to a single cell. You can however, reliably compare its Range.Address property to a single cell's Range.Address property.

此外，目标可以是一个单元或大量的单元。您无法可靠地将其与单个单元进行比较。但是，您可以可靠地比较它的范围。地址属性到单个单元格的范围。地址属性。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("AF1").Address Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Range("AA2:AA75") = Range("AB2:AB75").Value
    Else
        'something else...?
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Using Application.EnableEvents property tp disable events should only be done with error control rthat always turns it back on in case something goes wrong.

使用应用程序。启用事件属性tp禁用事件应该只在错误控制的情况下完成，当出现问题时，它总是会打开它。

#2

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = "$AF$1" Then
     ' Avoid copy then paste by assigning the value itself
     Target.Worksheet.Range("AA2:AA75").Value = Target.Worksheet.Range("AB2:AB75").Value

  End If

 End Sub

#1