I need a macro that runs other macros until P5>=28

J

Jeff

I need a goMacro that I can tell it when to start to run other macros until
Sheet1 P5>=28

I already have the following macros that do basically this (below) but I
have to click a separate buttons for each because I have to stop when the
cell that sums Sheet1 D5:L5 (Sheet1 P5) is equal to or greater than 28. Here
are my macros:

Macro1 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
D5:D6 then delete Sheet1 F1:F2 shift rows up.

Macro2 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
E5:E6 then delete Sheet1 F1:F2 shift rows up.

Macro3 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
F5:F6 then delete Sheet1 F1:F2 shift rows up.

Macro4 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
G5:G6 then delete Sheet1 F1:F2 shift rows up.

Macro5 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
H5:H6 then delete Sheet1 F1:F2 shift rows up.

Macro6 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
I5:I6 then delete Sheet1 F1:F2 shift rows up.

Macro7 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
J5:J6 then delete Sheet1 F1:F2 shift rows up.

Macro8 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
K5:K6 then delete Sheet1 F1:F2 shift rows up.

Macro9 = copy from Sheet2 cells F1:F2 then paste special values in Sheet1
L5:L6 then delete Sheet1 F1:F2 shift rows up.

When Sheet1P5>=28 run Macro10

So in other words, I click the goMacro button and Macro1 does it’s thing. If
Sheet1 P5>=28 then run Macro10, otherwise run Macro2 and so forth.

Thank you,
Jeff
 
D

Dave

Hi,
You could use a Do Until Loop.

Sub RunRoutines()
Do Until Sheets(1).Range("P5")>=28
Call Macro 1
Call Macro 2
Call Macro 3
Call Macro 4
Call Macro 5
Call Macro 6
Call Macro 7
Call Macro 8
Call Macro 9
Loop
Call Call Macro 10
End Sub

If you need to check the value of P5 between each macro run:

Sub RunRoutines()
Do Until Sheets(1).Range("P5")>=28
If Sheets(1).Range("P5")>=28 Then Call Macro 1 Else Goto Last
If Sheets(1).Range("P5")>=28 Then Call Macro 2 Else Goto Last
If Sheets(1).Range("P5")>=28 Then Call Macro 3 Else Goto Last
If Sheets(1).Range("P5")>=28 Then Call Macro 4 Else Goto Last
If Sheets(1).Range("P5")>=28 Then Call Macro 5 Else Goto Last
If Sheets(1).Range("P5")>=28 Then Call Macro 6 Else Goto Last
If Sheets(1).Range("P5")>=28 Then Call Macro 7 Else Goto Last
If Sheets(1).Range("P5")>=28 Then Call Macro 8 Else Goto Last
If Sheets(1).Range("P5")>=28 Then Call Macro 9 Else Goto Last
Loop
Last:
Call Macro 10
End Sub

Regards - Dave.
 
D

Dave

Sorry, typo in the second macro.
Should be:
Sub RunRoutines()
Do Until Sheets(1).Range("P5")>=28
If Sheets(1).Range("P5")<28 Then Call Macro 1 Else Goto Last
If Sheets(1).Range("P5")<28 Then Call Macro 2 Else Goto Last
If Sheets(1).Range("P5")<28 Then Call Macro 3 Else Goto Last
If Sheets(1).Range("P5")<28 Then Call Macro 4 Else Goto Last
If Sheets(1).Range("P5")<28 Then Call Macro 5 Else Goto Last
If Sheets(1).Range("P5")<28 Then Call Macro 6 Else Goto Last
If Sheets(1).Range("P5")<28 Then Call Macro 7 Else Goto Last
If Sheets(1).Range("P5")<28 Then Call Macro 8 Else Goto Last
If Sheets(1).Range("P5")<28 Then Call Macro 9 Else Goto Last
Loop
Last:
Call Macro 10
End Sub
 
J

Jeff

Hi Dave,

I tryed both of them: The first one only works if I type 28 in P4 but it
doesn't work with SUM(D5:L5) in P4

The second one only runs Macro10. It doesn't matter what's in P4 at all.

By the way, I am using version 2003.

What am I doing wrong?
 
D

Dave

Hi Jeff,
Did you get my second corrected macro? I was too hasty the first time.
I've tried running this with 28 typed in, and with 28 as the result of a
SUM, and the macro recognizes both. Not sure why it's not working for you. I
run XL2000, but I doubt that will be the issue.
You could perhaps try typing .Value after each instance of Range("P5")
ie Range("P5").Value
Perhaps one of the MVP's will offer their assistance.
Regards - Dave.
 
J

Jeff

Hi Dave,

Sorry for the late reply, I work evenings and I had a family emergency to
attend to last night... I haven't been able to try your second corrected
macro until recently.

Anyway, it works great! and I really appreciate your help.

Thank you,

Jeff
 
D

Dave

Hi Jeff,
Hope your emergency is easing.
Pleased to help with the macro.
Thanks for the feedback.
Regards - Dave, Kiwi in Brazil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top