Run multiple macros()

[edlim85]

hi there,


I want to have a option to run all my macros or indivdual macro..Currently, I have 29 unique macros each behind in a specific worksheet in vba.(ie in VBA worksheets(TU372) i have Sub TU372()... I cant use Call..it gives me "Run Time Error 1004".

Call TU372
Call TU375
Call TU397



Im new to vBA, previously i store all my macros in a module. but i cant run it in ALT-F8. how do i solve this??

 

[edlim85]

Im searching for answers also.

what is option explicit? can i use this?

Option Explicit

Sub Test1()
Application.Run ("TestVBSub")
Application.Run ("TestXLM")
End Sub

 

[p45cal]

try missing out the Call bit, that is, just use the name of the macro by itself on a line.

 

[p45cal]

A bit more is needed:

Sheets("TUA1").TUA1
Sheets("TUA2").TUA2
Sheets("TUA3").TUA3

should work, perhaps safer though to use the codename (the part not in parentheses in the Project Explorer pane) of the sheets:

Sheet1.TUA1
Sheet2.TUA2
Sheet3.TUA3

(I've guessed the codenames)

 

[edlim85]

Hi p45cal,


im wondering, Whats is the different if i store each macro in a module instead in the sheet?
ie i will have 29 modules

 

[p45cal]

Hi p45cal,


im wondering, Whats is the different if i store each macro in a module instead in the sheet?
ie i will have 29 modules

That's more like it - much better, then it's more like my first answer.
You can store all the macros in a single code module.

These macros, are they very similar to each other? If so, what's the code?; it's likely that a single macro can be written which acts on whichever sheet(s) you wish.

 

[Sandeep Warrier]

If you are using XL2007 or higher, giving a name of TU1 etc, to a macro may create issues, since this is also a cell in Excel.

Using "Option Explicit" requires you to declare variables.

On using Option Explicit, the code below will result in an error, since strMessage has not been declared.

Option Explicit

Sub Test()
    strMessage = "Hello World"
    MsgBox strMessage
End Sub

To get this to run properly, you will need

Option Explicit

Sub Test()
    Dim strMessage As String
    strMessage = "Hello World"
    MsgBox strMessage
End Sub