Automatically run macros when data changes

Cosmin Gheoca

New Member
Joined
Jul 3, 2014
Messages
43
Hi, I have a workbook with 3 worksheets, Sheet 2 and Sheet 3 containing multiple Macros. I need these macros to run automatically when I change data in the sheets.
How do I do that?

Regards!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi

For Changes to the Worksheets in general you need to put your macro in to or include a call referance to the macro in the Worksheets Change Event. For Changes to specific ranges of your worksheet you will need to review the Intersct Method

Code:
<CODE>Worksheets("Sheet1").ActivateSet isect = Application.[B]Intersect[/B](Range("rg1"), Range("rg2"))If isect Is Nothing Then    MsgBox "Ranges do not intersect"Else    isect.SelectEnd If</CODE></PRE>

[/CODE
 
Upvote 0
Cosmin Gheoca,

You will need to use Change Event.

eg to run macros whenever any change in any sheet within the workbook then in the Workbook module...

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)'To happen when any change antwhere in workbook
MsgBox "Call your code"
MsgBox "Call code"
'Call xyz ' xyz is a macro name
'Call abc ' abc is another macro name  etc

End Sub


Or on a sheet by sheet basis in the desired sheet's code module....

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'To happen when any change in this worksheet
MsgBox "Call code"
'Call xyz ' xyz is a macro name
'Call abc ' abc is another macro name  etc
End Sub

Hope that helps.
 
Upvote 0
Hi Snakehips,

The macros I have are the ones you suggested me in a previous thread:
Sub merge()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 1)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub

. I have 5 of these on each sheet (Sheet 2 and Sheet 3) referring to different columns. Ideally I will need to run each macro whenever there are changes in the 2 columns that the macros take data from.( In the case above columns A,B.)

Can you please edit the above code to do this, and I will adapt for each of the macros I have?

Regards!
 
Upvote 0
Ok, so now I have 2 codes, one for each sheet (Sheet 1 and Sheet 2), each code having 5 of the macros I detailed earlier. Ideally I want to run this code every time a change comes up in the range $AP:$BG for sheet 1, and range $AX:$BO for sheet 2.
Can you guide me on how to add the change event?

Regards!
 
Upvote 0
Ok, so I have 2 codes, one for each sheet (Sheet 1 and Sheet 2), each code having 5 macros like the one below, each for a different range of columns.

Sub merge()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 1)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub

Ideally I want to run this code every time a change comes up in the range $AP:$BG for sheet 1, and range $AX:$BO for sheet 2.
Can you guide me on how to add the change event?

Regards!
 
Upvote 0
Do you want to restrict triggering the macros to when the change is manual change of value in a single cell or do you also want it to trigger if there is a bulk change such as a paste?

Can you confirm your actual sheet names, and the range for each?

Confirm also the names of the subs to be run for each sheet?
 
Upvote 0
Sheet1(Circular)

Sub mergeAP()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "AP").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 42)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub
Sub mergeAT()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "AT").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 46)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub
Sub mergeAW()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "AW").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 49)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub
Sub mergeBA()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "BA").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 53)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub
Sub mergeBF()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "BF").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 58)




If .Offset(0, 1) = "" Then
.Offset(s, 4) = .Value
Else
.Offset(s, 4) = .Offset(0, 1).Value
.Offset(s + 1, 4) = .Value
s = s + 1
End If
End With
Next r
End Sub


Sheet2(Hyperbolic)

Sub mergeAX()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "AX").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 50)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub
Sub mergeBB()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "BB").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 54)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub
Sub mergeBE()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "BE").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 57)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub
Sub mergeBI()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "BI").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 61)




If .Offset(0, 1) = "" Then
.Offset(s, 2) = .Value
Else
.Offset(s, 2) = .Offset(0, 1).Value
.Offset(s + 1, 2) = .Value
s = s + 1
End If
End With
Next r
End Sub
Sub mergeBN()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "BN").End(xlUp).Row
s = 0
For r = 2 To lr
With Cells(r, 66)




If .Offset(0, 1) = "" Then
.Offset(s, 4) = .Value
Else
.Offset(s, 4) = .Offset(0, 1).Value
.Offset(s + 1, 4) = .Value
s = s + 1
End If
End With
Next r
End Sub


Sheet 1 range is $AP:$BG
Sheet 2 range is
$AX:$BO

The values in these ranges are given by formulas, and they will be locked for the user. Ideally I don't want to use a button to run the codes, they should trigger automatically.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top