asked on

How to create a macro to run 4 different macros on 4 different sheets?

Right now I have a macro that clears certain cells on a sheet. I have 4 sheets, 4 macros and a button on each sheet to run each macro. I would like to create a macro that will run all 4 macros for their respective sheet and assign it to a button on my main sheet.

Sheet #            Sheet Name      Name of macro
Sheet1            HPSM            Clear_Cells
Sheet7            EVIP-VIP        Clear_Cells_EVIP
Sheet8            PCA            Clear_Cells_PCA
Sheet9            PCR            Clear_Cells_PAR

Saqib Husain

Use

Sheet1.Clear_Cells
Sheet7.Clear_Cells_EVIP
Sheet8.Clear_Cells_PCA
Sheet9.Clear_Cells_PAR

or

Sheets(HPSM).Clear_Cells
Sheets(EVIP-VIP).Clear_Cells_EVIP
Sheets(PCA).Clear_Cells_PCA
Sheets(PCR).Clear_Cells_PAR

Norie

Try this.

Dim arrMacros As Variant
Dim I As Long

    arrMacros = Array("Sheet1.Clear_Cells", "Sheet7.Clear_Cells_EVIP", "Sheet8.Clear_Cells_PCA", "Sheet9.Clear_Cells_PAR")

    For I = LBound(arrMacros) To UBound(arrMacros)
        Application.Run arrMacros(I)
    Next I

Open in new window

kbay808

ASKER

No joy for all 3. I attached a screen shot for all 3 for your review
Syed-Fix-1.jpg
Syed-Fix-2.jpg
Imnorie-Fix.jpg

Saqib Husain

Sheets("HPSM").Clear_Cells
Sheets("EVIP-VIP").Clear_Cells_EVIP
Sheets("PCA").Clear_Cells_PCA
Sheets("PCR").Clear_Cells_PAR

kbay808

ASKER

I’m still getting an error. I'm running Office 2010 just in case that makes a difference.
Syed-Fix-3.jpg

Norie

Can you post the code for the macros and/or attach a sample workbook?

kbay808

ASKER

As requested
Sample-Workbook.xlsm

DrTribos

I think this is what you are asking for:

Sub callMacros()
    Call Module4.Clear_Cells_EVIP
    Call Module3.Clear_Cells_PCR
    Call Module2.Clear_Cells_PCA
    Call Module1.Clear_Cells
End Sub

Open in new window

Note that you can combine Modules 1-4 without harming your project (unless you have specific reasons for keeping them separate).

Hope it helps

kbay808

ASKER

DrTribos

That will run all of the macros on the same sheet. I only want to run the specific macro for each specific sheet.

kbay808

ASKER

As a different approach, can the sheet be assigned in the individual macros? That way it does not matter where the macro is ran from.

DrTribos

Yes that would be the way to do it... you can use named ranges or address the sheet:

ActiveWorkbook.Sheets("EVIP-VIP").Range("C10").ClearContents

ASKER CERTIFIED SOLUTION

DrTribos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrTribos

By the way... instead of

     .Range("B4").ClearContents
     .Range("B6").ClearContents
     .Range("B7").ClearContents
     .Range("B8").ClearContents

Open in new window

you might as well just have

.Range("B4:B8").ClearContents

Open in new window

particularly if B5 will be blank anyway...

kbay808

ASKER

It worked!!! Thank you very much.

kbay808

ASKER

Great work!

DrTribos

Glad to help, thanks for the grade :-)