Automatically run macros when data changes

[Cosmin Gheoca]

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!

 

[Snakehips]

Thanks for that.
Sorry to appear a pain but.....
You say that the ranges are formulas. Can you narrow down what action by the user will cause that change? The reason I ask is that unless we can code for a specific manual change event so that the code runs only when you really need it to, then we have to be more general and trigger the code every time anything changes. In the latter case the code may run every time anything changes and may slow your worksheet down. The Worksheet Change event does not recognise the change of value brought about by a formula but there must be some manual change of data that causes the formula result to change. We need to identify that manual event. IF we use the Workbook Sheet Change event it will fire the code for each and every manual change in the workbook. IF you can't be more specific then is there a manual change in sheet Circular that triggers change in the that sheet and the other code is triggered by a change in the Hyperbolic sheet?

 

[Cosmin Gheoca]

To start answering your question, my workbook is structured so that the end-user cannot even touch Circular and Hyperbolic sheets. Data used by these sheets is taken from sheet 3 and the end user can only change that entire data as he wishes. Further more, that data can also be a live feed data, adding rows at each pre-established time interval, so basically there will be no manual changes ever.
A solution would be if we could change the macros used to other excel functions to stack the 2 columns.

 

[Cosmin Gheoca]

The data the workbook uses is given by an AddIn.

 

[Snakehips]

Sorry, but I'm not confident that I know how best to proceed with this.

So are you are saying that either the user can force an update in sheet 3 or it is automatically updated on a timed basis?
I'm not sure that using the worksheet change event for sheet 3 would be practical as it would trigger at every cell update unless the update code was able to satisfactorily disable events.
I would suggest that maybe the code that is doing the updating needs to call the subs mergeAP and mergeAX once it has finished updating sheet 3.

In that case mergeAP and merge AX will need to be edited slightly to ensure that the appropriate sheet is being operated on.

 

[Cosmin Gheoca]

It takes more than 10 minutes to run one merge macro on 1000 lines. Is that normal or am I doing something wrong? I have added
Sub RunAllMacros()
mergeAP
mergeAX
etc..
end sub
Sub merge AP
....in order to be able to assign one button for all 5 and it took 30 minutes to end when I ran it.

 

[Snakehips]

Without a better understanding of the size of your data, the amount of calculation it is performing, where in the process you are calling the subs etc it is difficult say. Certainly 10 minutes sounds a long time for what should be a one off looping through max 1000 lines twice.
Are you able to share a sample file?

 

[Cosmin Gheoca]

I ran one of the macros in a sample sheet and it took half second to be performed. My 5 macros are preceded by Sub RunAllMacros() so they run all at the click of a button.

Sub RunAllMacros()
mergeAX
mergeBB
mergeBE
mergeBI
mergeBN
End Sub
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


Something in there works bad but I don't know why

 

[Cosmin Gheoca]

The code works fine when we have a clean table with values only on the columns referred in the code. When we have other information it takes forever to process.

 

[Snakehips]

Maybe try on a backed-up file...

Sub RunAllMacros()
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
mergeAX
mergeBB
mergeBE
mergeBI
mergeBN
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate
End Sub

 

[Cosmin Gheoca]

Works great, thank you very much!