Macro for running multiple macros in a sequence!

[kkaass]

Hi,

I would like to run a macro which consists of two macros which run in a sequence.

So say the first macro is Macro1 and the second is Macro2. Here's what I have

Private Sub Worksheet_Change (ByVal Target As Range)
Select Case Target.Column
Case is=4,5,6,7
Application.EnableEvents =False
Call Macro1
Application.EnableEvents =True
End Select
Call Macro2
End Sub

I tried the above command but it is not working. Infact, the part till 'End Select' is not working correctly when I add 'Macro2' after that. However, if I exclude 'Call Macro2' from this command, then it runs properly without any problems or errors.

Can someone please let me know the error in the above code? So basically till 'End Select' I want the command to run first, and then after that I would like Macro2 to run automatically.

Any help would be greatly appreciated!

 

[Mike LH]

Hi,

What happens for you because it works fine for me?

 

[kkaass]

Hi,

Thanks alot for responding. Well in my case Macro 2 is not running. So the macro runs fine till 'End Select' but after that Macro 2 does not work. So for example, if I change any value in column F (as per my previous command, because I used Select Case command), Macro 1 runs automatically, but after Macro 1 runs, Macro 2 does not run automatically, as it should.

So I was wondering if I have to specify somehow that once Macro 1 runs, then Macro 2 should run immediately afterwards, maybe! But I am not sure how to do that!

Does it have to do anything with the 'Application EnableEvents' command?

Thanks again! I will be very grateful if you can help me out.

 

[mas123]

Try to remove this lines
Application.EnableEvents

 

[kkaass]

Hi,
Okay, I tried by removing Application.EnableEvents command, now it says 'Run-time error '28', out of stack space

Is it because I have one command in each Macro 1 and Macro 2 which is same so the following command appears in both Macro 1 and Macro 2

Range ("N2")=DMUNo, this command appears in both Macro 1 and Macro 2, does that create any problem? Maybe thats why both Macro 1 and Macro 2 are not running one after another in one command.

 

[mas123]

can you write the code of macro1 and macro2
?

 

[kkaass]

So Macro 1 is as follows:
Sub Macro1 ()
Dim DMUNo As Integer
For DMUNo = 1 To 15
Range("N2") = DMUNo
SolverSolve UserFinish:=True
With Range("I1")
.Offset(DMUNo, 0) = Range("O3")
End With
Next DMUNo
End Sub

and

Macro 2 is:
Sub Macro2()
Dim DMUNo As Integer
For DMUNo = 1 To 15
Range("N2") = DMUNo
SolverSolve UserFinish:=True
Range("P4:P7").Select
Selection.Copy
Range("U" & DMUNo + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Next DMUNo
End Sub

When run separately, these two commands run just fine!

 

[mas123]

Try this merged

Private Sub Worksheet_Change (ByVal Target As Range)Dim DMUNo As Integer
For DMUNo = 1 To 15
Range("N2") = DMUNo
SolverSolve UserFinish:=True


Select Case Target.Column
Case is=4,5,6,7
With Range("I1")
.Offset(DMUNo, 0) = Range("O3")
End With
End Select


Range("P4:P7").Select
Selection.Copy
Range("U" & DMUNo + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True


Next DMUNo
End Sub
Tell me the result

 

[kkaass]

Thank you very much for all your help!

Actually I tried to change the number in column F because thats how its supposed to work in the first place, I change a number in any of the four columns mentioned and then Macro 1 should run and after that Macro 2. So by applying the above command I got the following:

Run-time error '()'
Method '_Default of object 'Range' failed

 

[mas123]

where's the position of this error?
in any range word