CODEWITHSUNDEEP
excelvba
adifadipe
adifadipe

Reputation: 1

Run multiple macros in a workbook from another workbook

I have a Master Macro workbook which consists of multiple macros, basically it consists of a Generate Separate Files worksheet which has a dropdown to select a specific values and there is button next to that cell to run macro for the selected value.

So the moment I select the value in the Generate Files worksheet cell and hit run macro button, it first does the save as of the master macro file itself with the name/value I had selected in the cell and then filters out the data from each of the worksheets in the master macro workbook to retain only the data of the selected value.

So like this I have to open the master macro file workbook multiple times select the value in Generate Separate Files worksheet cell and click run macro. I want to create another macro workbook which can open the master macro workbook, select value in the validation dropdown of "Generate Separate Files Worksheet" and run the macro for each value and generate the separate files for each selected files.

Sub Runmasterfilemacros ()
Dim wb1 as workbook
Dim wb2 as workbook
Dim sht1 as worksheet
Dim sht2 as worksheet   
Dim myrange as Range        

Set wb1 = ThisWorkbook
Set wb2 = ThisWorkbook.Sheets("Run Macro").Range("A2").Value
' This Workbook Run Macro Range A2 value = "c:\macrofiles\Master Macro File.xlsm"
Set sht1 = ThisWorkbook.Sheets("Run Marco")
Set sht2 = Workbooks("Master Macro File").Sheets("Generate Separate Files")
Set myrange = Workbooks("Master Macro File").Sheets("Generate Separate Files").Range("A11").Value

wb2.Select
sht2.select
myrange.select
myrange.value  = "File1"
Application.Run "File1Macro"
'wait for the macro to run and the workbook to get saved as File1 then run another macro

wb1.Activate

wb2.Select
sht2.select
myrange.select
myrange.value  = "File2"
Application.Run "File2Macro"
'wait for the macro to run and the workbook to get saved as File2 then run another macro

wb2.Select
sht2.select
myrange.select
myrange.value  = "File3"
Application.Run "File3Macro"
'wait for the macro to run and the workbook to get saved as File3 then run another macro

wb2.Select
sht2.select
myrange.select
myrange.value  = "File4"
Application.Run "File4Macro"
'wait for the macro to run and the workbook to get saved as File4 then run another macro

wb2.Select
sht2.select
myrange.select
myrange.value  = "File5"
Application.Run "File5Macro"
'wait for the macro to run and the workbook to get saved as File5 then run another macro

'This needs to run for all the values which are in the validation dropdown of myrange .i.e. 25 files
End Sub

Upvotes: 0

Views: 59

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

This will open the master workbook (if not already open), step through each value in the DV source list, and run the macro for each value.

Sub Runmasterfilemacros()
    Const MASTER_WB_PATH As String = "C:\Temp\"
    Const MASTER_WB_NAME As String = "Master Macro File.xlsm"
    Dim wbM As Workbook, dvRange As Range, listRange As Range, c As Range
    
    On Error Resume Next                'ignore error if workbook is not open
    Set wbM = Workbooks(MASTER_WB_NAME) 'safer to include the extension
    On Error GoTo 0                     'stop ignoring errors
    'open the master workbook if it wasn't already open
    If wbM Is Nothing Then Set wbM = Workbooks.Open(MASTER_WB_PATH & MASTER_WB_NAME)
    
    With wbM.Worksheets("Generate Separate Files")
        Set dvRange = .Range("A11")      'cell with drop-down
        Set listRange = .Range("H2:H26") 'for example; your list of values
    End With
    
    For Each c In listRange.Cells  'loop the range of list values
        dvRange.Value = c.Value    'set a value
        Application.Run "'" & wbM.Name & "'!FileExportMacro" 'run the macro
    Next c
End Sub

The macro you're calling needs to be Public in a regular module, or if it's (eg) in a worksheet module you need to add the sheet codename eg:

Application.Run "'" & wb2.Name & "'!Sheet2.FileExportMacro"

Upvotes: 0

Related Questions