|
I need a macro that runs other macros until P5=28
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 its thing. If Sheet1 P5=28 then run Macro10, otherwise run Macro2 and so forth. Thank you, Jeff |
I need a macro that runs other macros until P5=28
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. "Jeff" wrote: 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 its thing. If Sheet1 P5=28 then run Macro10, otherwise run Macro2 and so forth. Thank you, Jeff |
I need a macro that runs other macros until P5=28
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 |
I need a macro that runs other macros until P5=28
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? "Dave" wrote: 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. "Jeff" wrote: 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 its thing. If Sheet1 P5=28 then run Macro10, otherwise run Macro2 and so forth. Thank you, Jeff |
I need a macro that runs other macros until P5=28
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. "Jeff" wrote: 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? "Dave" wrote: 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. "Jeff" wrote: 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 its thing. If Sheet1 P5=28 then run Macro10, otherwise run Macro2 and so forth. Thank you, Jeff |
I need a macro that runs other macros until P5=28
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 "Dave" wrote: 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. "Jeff" wrote: 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? "Dave" wrote: 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. "Jeff" wrote: 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 its thing. If Sheet1 P5=28 then run Macro10, otherwise run Macro2 and so forth. Thank you, Jeff |
I need a macro that runs other macros until P5=28
Hi Jeff,
Hope your emergency is easing. Pleased to help with the macro. Thanks for the feedback. Regards - Dave, Kiwi in Brazil "Jeff" wrote: 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 |
| All times are GMT +1. The time now is 12:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com