3

I have a worksheet where in the first three columns (A,B,C) I store data. These values are used in a macro.

I would like to know how it is possible to make this macro run automatically after data is pasted onto these columns. I am almost sure that I will use the Worksheet-Change module, but as for the code I am clueless.

Thanks in advance.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Dubeddo
  • 135
  • 2
  • 4
  • 12

1 Answers1

2

A simple implementation:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:C" & ThisWorkbook.Worksheets(1).UsedRange.Rows.Count))      Is Nothing Then
    'Call your Macro to do stuff
    End If
End Sub

Intersect checks if Target is in the range you want to monitor. So if something changes in columns past C, Intersect will return Nothing and your macro won't be called. Just keep in mind that the Worksheet_Change event fires on any change, even double clicking into the cells. If all you are doing in this Worksheet is copy&pasting data and then running your Macro, this should be fine, but if you are manipulating your data further, you might have to look at more sophisticated solutions. Examples include mirroring your Worksheet and comparing it pre/post Worksheet Changed Event. You can read more on this here: Detect whether cell value was actually changed by editing

Community
  • 1
  • 1
Haris
  • 778
  • 2
  • 9
  • 20