Automatically Call a Sub

[jpstory]

Hi Everyone

I am trying to let excel call a simple Copy&Paste Sub automatically every time when I input data into a specific cell, but I couldn't figure out how to, any help will be greatly appreciated. Thank you in advance.


Here is the idea: (please ignore the dots between the two 1s, that's for display purpose only,to separate the two numbers here)

A1 B1 C1
1 . . . .1

How can I let excel automatically call the Sub which copies 1 from Cell A1 to Cell C1 every time when I input something into Cell B1. And if I input new data into Cell B1 again, the Sub will simply re-run again.

Most importantly, I will be doing this for every single row on the worksheet.
So whenever I input data into a row in Column B, the Sub will run on that row.

I tried to use the Change Event but still couldn't figure it out.

Thanks

 

[jbeaucaire]

Right-click the sheet tab and select View Code.

In the Sheet module that appears, paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

Application.EnableEvents = False
For Each cell In Target
    If cell.Column = 2 Then
        Range("C" & cell.Row).Value = Range("A" & cell.Row).Value
    End If
Next cell
Application.EnableEvents = True
    
End Sub

Close the editor and save your sheet. Try it out.

 

[jpstory]

Hi,jbeaucaire
Thanks, it worked out perfectly for me!
Just got one more question regarding the code. C
ould you please add come brief explanation on the following lines, I am trying to master it so I will be able to duplicate it and customize it next for other projects.

Especially this line "cell.column = 2" and "For Each cell in Target", and how do they work together to make this code work. Greatly appreciate it in advance and thank you again.

Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range Application.EnableEvents = False For Each cell In Target If cell.Column = 2 Then Call My_Own_Marco End If Next cell Application.EnableEvents = True End Sub
</pre>

 

[jbeaucaire]

Many people configure ws_Change macros so that if you change more than one cell at a time, the macro aborts. I don't like that, so I construct mine to look at each cell in the "just changed cells" and evaluate them indendently.

So we use the object I created called "cell" and check each one's column to see which column it is in. If it is in column 2, or column "B", it continues. Any other column and it skips.

Also, ws_change events are triggered anytime a change occurs on the sheet, so of the macro itself is going to make a change, like yours is, then we have to turn OFF all "event" macros until we're done to insure the macro doesn't trigger itself again. That's what EnableEvents is for.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range                   'this object will be used to check all changed cells

Application.EnableEvents = False    'turn off events until we're done
For Each cell In Target             'loop though all the changed cells one at a time
    If cell.Column = 2 Then         'only proceed if this is in column B
        Range("C" & cell.Row).Value = Range("A" & cell.Row).Value
    End If
Next cell
Application.EnableEvents = True     'turn event macros back on
    
End Sub