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
kbay808Asked:
Who is Participating?
 
DrTribosConnect With a Mentor Commented:
I'd suggest changing your macro to a single macro:
Sub CleanUp()

With ActiveWorkbook
 .Sheets("EVIP-VIP").Range("C10").ClearContents
 .Sheets("PCR").Range("B2:B3").ClearContents
    With .Sheets("PCA")
     .Range("B4").ClearContents
     .Range("B6").ClearContents
     .Range("B7").ClearContents
     .Range("B8").ClearContents
    End With
 .Sheets("HPSM").Range("B1:B11").ClearContents
End With

End Sub

Open in new window



The "With .... End With" blocks are a kind of shorthand & code optimisation; they group things that are in the same place so they only need to be found once.
0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
NorieVBA ExpertCommented:
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

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kbay808Author Commented:
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
0
 
Saqib Husain, SyedEngineerCommented:
Sheets("HPSM").Clear_Cells
Sheets("EVIP-VIP").Clear_Cells_EVIP
Sheets("PCA").Clear_Cells_PCA
Sheets("PCR").Clear_Cells_PAR
0
 
kbay808Author Commented:
I’m still getting an error.  I'm running Office 2010 just in case that makes a difference.
Syed-Fix-3.jpg
0
 
NorieVBA ExpertCommented:
Can you post the code for the macros and/or attach a sample workbook?
0
 
kbay808Author Commented:
As requested
Sample-Workbook.xlsm
0
 
DrTribosCommented:
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
0
 
kbay808Author Commented:
DrTribos

That will run all of the macros on the same sheet.  I only want to run the specific macro for each specific sheet.
0
 
kbay808Author Commented:
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.
0
 
DrTribosCommented:
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
0
 
DrTribosCommented:
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...
0
 
kbay808Author Commented:
It worked!!!  Thank you very much.
0
 
kbay808Author Commented:
Great work!
0
 
DrTribosCommented:
Glad to help, thanks for the grade  :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.