Run Macro automatically when data is PASTED into a table

**tabira** · 04-16-2012, 03:32 AM

Hi Everyone. I need to run a macro ("ReformatData") everytime that someone adds or changes data in a table. So far I have the code below in the VBA project box (Excel 2010) which only works when I "manually" select a cell and change the data, but not when I right click to insert new data or operate "control V" to paste new data over old (which is 99% of the time)

Could any one suggest a code which will run the macro when data is pasted, inserted or "manually" changed but NOT when a superficial alteration is made such as colour formatting or using the automatic filters? Thanks in advance for giving this some thought.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("$A$31:$L$10000")) Is Nothing Then

Call ReformatData
Range("A1").Select
MsgBox "Reformaté"
End If
End Sub

**tabira** · 04-17-2012, 04:54 AM

No one out there can help me here? I thought this would be an easy one!!! LOL

**Mallycat** · 04-17-2012, 05:38 AM

I am using Excel 2010. Paste and insert events all trigger a change event, regardless if it is in a table.
Superficial changes as you describe do not trigger a change event.
Maybe it is an excel 2007 issue

**tabira** · 04-17-2012, 07:15 AM

Actually I work off the same file with Excel 2007 at work and Excel 2010 at home but the automtaic macro doesn't work on either for pasting data, only for changing it by manually selecting a cell. I'm mystified because as you say, pasting is a change event for excel 2010 so it should work at home. (I'm pretty sure that pasting is a change even for 2007 too but not 100%) Is there something missing in the code?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("$A$31:$L$10000")) Is Nothing Then

Call ReformatData
Range("A1").Select
MsgBox "Reformaté"
End If
End Sub

**mrice** · 04-17-2012, 07:22 AM

Have you tried adding a breakpoint at the start of your Worksheet_Change code to see if the event is triggered at all?

It's good practice to disable further events while a change event is running. Is there an Application.EnableEvents = False line somewhere in your code or Reformat macro which is turning event handling off?

**tabira** · 04-17-2012, 07:45 AM

"Have you tried adding a breakpoint at the start of your Worksheet_Change code to see if the event is triggered at all?"
WOW! just as I was waiting for your reply, I re-copied the code from this forum into the VBA box forgetting that I had translated the names of the macro from French to English just so it didn't look wierd on and English forum. I then tried to rerun it and it came up with a VBA error message because the macro name didn't exist. I though to myself that's a good sign, at least it tried to call the macro whereas before nothing happened. I corrected it, re run it, and it worked fine!!!!! I checked an earlier version of the same file from yesteday to see if I hadn't just mistyped something stupid before but no. The code today that works is exactly the same as the code yesterday that didn't work, only that by "breaking it" , it corrected itself. Wierd! Wierder still I come back to the forum to tell you this and then see that you had just suggested that I do the same thing! Thanks, you were obviously right! Maybe osmosis? So something with excel breaking something that isn't "broken", repairs it?!

have a nice day
Dante

**tabira** · 04-17-2012, 08:01 AM

My previous reply was too hasty - I now realise that it only works once after "breaking and fixing" the code. I'd have to break and fix it each time for it to work, which is absurd of course. Here's the macro that I'm trying to call automatically: It just reformats the newly pasted text so that the table is presentable at all times. So I went back to your reply and found the answer in it - the line in the macro " "Application.CutCopyMode = False"" was disabling the change event macro. I took it out and now it works every time. Thanks a million.

Sub SasieReformater()

Range("SasieReformaterEssaiPayant").Select
Selection.Replace What:="TEST PERIOD", Replacement:="Essai Payant", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("SasieReformaterZone").Select
Selection.Replace What:="5", Replacement:="Monde", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="4", Replacement:="Asie", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="3", Replacement:="EU", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="2", Replacement:="Am. du N/S", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="1", Replacement:="Trois Pays", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("SasieReformaterPack").Select
Selection.Replace What:="999", Replacement:="Std", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="998", Replacement:="EV", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="997", Replacement:="CL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="996", Replacement:="EP", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="995", Replacement:="CE", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="994", Replacement:="V", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="Pub HP", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("SasieReformaterLignesType").Select
Selection.Copy
Range("SasieReformaterLignesDonnées").Select
Range("SasieReformaterLignesColler").Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
Range("A1").Select

End Sub