CODEWITHSUNDEEP
excelvba
Stack Lost
Stack Lost

Reputation: 25

Excel VBA Cut or copy and paste on change of cell

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)

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

If Target = Range("AF1") Then

Upvotes: 1

Views: 3043

Answers (2)

Julien Marrec
Julien Marrec

Reputation: 11895

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

Upvotes: 0

user4039065
user4039065

Reputation:

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.

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.

Upvotes: 1

Related Questions