VBA Macro w/in a Macro ?

[Pinaceous]

Hi All;

I have a quick ? with a Macro w/in a Macro recorded code.

Let me try to explain it, here goes...


Below is a Macro that I recorded that containes several written Macros, as you can see.

Sub Macro10()
'
' Macro10 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'

Application.Run "'Excel-File-Name-1.xlsm'!Macro12"
Application.Run "'Excel-File-Name-1.xlsm'!Macro1"
Application.Run "'Excel-File-Name-1.xlsm'!Macro8"
Application.Run "'Excel-File-Name-1.xlsm'!Macro9"
Application.Run "'Excel-File-Name-1.xlsm'!Macro4"
Application.Run "'Excel-File-Name-1.xlsm'!Macro13"
Application.Run "'Excel-File-Name-1.xlsm'!Macro5"
Application.Run "'Excel-File-Name-1.xlsm'!Macro6"

End Sub

My question is; when you rename the file(ie 'EXCEL-File-Name-2.xlsm'), it will not execute the command macro because the file name has since been changed.

Is there anyway around this, in terms of having a command macro, which containes a string of written macros for its a file name, where it is then copied and renamed to a different file name, where this command macro can still function w/o having to go into the VBA in order to change its name being; Application.Run "'Excel-File-Name-2.xlsm'!Macro##" ?


Hope my point is clear.

Thanks in advance.

R/

-Pin

 

[mrmmickle1]

You can set the filename as a string. So then instead of changing the code in 20 different places you can simply change the one line.

For Example:

[COLOR=#0000ff]Dim [/COLOR]FileName[COLOR=#0000ff] As String[/COLOR]

FileName = "Your FileName"

Application.Run "'" & FileName & "'!Macro6"
Application.Run "'" & FileName & "'!Macro7"
Application.Run "'" & FileName & "'!Macro8"

or maybe put the macros in your Personal Workbook then there is no need to Qualify them like that...

 

[Pinaceous]

Thanks very much, I'm going to try it now
R/
-Pin

 

[jtakw]

Why can't you just Call the macros in your main macro?

i.e.

Sub Macro10()
 '
 ' Macro10 Macro
 '
 ' Keyboard Shortcut: Ctrl+Shift+Y
 '

 Call Macro12
Call Macro1
Call Macro8
Call Macro9
 Call Macro4
Call Macro13
Call Macro5
Call Macro6

 End Sub

 

[Pinaceous]

jtakw,

Wow! You are awsome!

-Pin

 

[jtakw]

jtakw,

Wow! You are awsome!

-Pin

You are welcome, glad it helped.

I'm still learning myself.